PDA

View Full Version : Dumb/Restore on a windows machine



partysafarii
11-02-2009, 05:33 AM
Hi,

I found a good article (http://www.linuxinsight.com/optimize_postgresql_database_size.html#comment-538) for database maintenance and would like to know, if the Dumb/Restore process is compatible to a windows installation as well.


Best regards,
partysafarii

netsrak
11-02-2009, 07:37 AM
In general yes but there may be some different filenames/path under windows.

partysafarii
11-02-2009, 10:18 AM
Do you think that someone would be able to translate and transer the posted linux shell into windows compatible commands? Is it possible to do it via the basic windows console? Don't find any PostgreSQL shell.


1.log in as pmx - we'll do everything as pmx, superuser privileges are not needed.
2.pmx-database stop - we need to stop database before the next step, to capture consistent data.
3.cp -av ~pmx/../postgres /somewhere - this is the most important step of all, so you need to do it carefully. If anything goes wrong with the following steps, you'll always be able to recover your database from the backup we made by doing this recursive copy.
4.edit pg_hba.conf - you need to remove all lines that allow access through the IPv4 sockets. That's because we'll need to have database up & running to made a dump of it, but we must not allow other services to use it (change the data in it) and thus make the dump/reload step inconsistent.
5.pmx-database start - start the database.
6.pmx-database dump dumpfile - dump the contents of the database to dumpfile.
7.dropdb pmx_quarantine - drop the old database.
8.createdb pmx_quarantine - recreate the database. It will be empty after this step.
9.pmx-database import dumpfile - finally, with this command we're importing the old data, but after this command finishes, the database will be compacted and will take much less space on disk. This will also have positive effect on the performance of the whole system.
10.pmx-database stop - stop the database to reconfigure it once again.
11.restore original pg_hba.conf - allow all users of the database to connect.
12.pmx-database start - start it.
13.(optional) pmx-httpd restart - if you encounter problems accessing your quarantine, restart the EUWI service and everything will work once again.


Dumb/Restore seems to much faster and effective than the basic maintenance. I would really like to test it. Do you have any experience with that, especially with the new PostgreSQL version? Maybe it's an idea to integrate the process into pg_admin.


Best regards,
partysafari

netsrak
11-02-2009, 02:53 PM
You can use the windows command box.
I guess you need to "cd" to the postgresql bin folder. If you find all the commands there it should be no problem to go through the steps.