Howto Backup PostgreSQL Databases Server With pg_dump command

Recently I had do to a lots of PostgreSQL database administration as I needed to move several databases onto  a production server.  PostgreSQL is one of the most robust, open source database servers available, and for my money, faster and generally better than MySQL. Like MySQL database server, it provides utilities for creating a backup.

Backup database using pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time.

$ pg_dump table | gzip -c > table.dump.tar.gz

Another option is use to pg_dumpall command. As a name suggest it dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:

$ pg_dumpall | gzip -c > all.dump.tar.gz


Moving the Default PostgreSQL Data Directory (Windows)

PostgreSQL for Windows installs the PGDATA directory by default into “C:\<Program Files>\PostgreSQL\some version\data”.  While is usually ok for development and even some production environments, it can be somewhat limited given the typically large amount of disk activity on (what is normally) the system volume.

This step-by-step post aims to explain the fastest way to change the default PGDATA directory to another location.

Step 1:

Close all application that are currently connected to your database, then go to Windows Services Management and stop the PostgreSQL service.  Naturally, the fastest way to get to Windows Services Management is by right clicking on my computer -> manage, and then click on the services node in the computer management tree:

Once the Postgres service is stopped, leave computer the Computer Management Console open (we’ll need it again in a second).

Step 2:

Copy the “C:\<Program Files>\PostgreSQL\some version\data” directory to the new location.  In my own attempts, moves do not seem to work, but the redundant disk usage should be pretty minimal and at least you will have an old backup in the default location if something goes wrong.

Step 3 (Very Important):

Rick click on the new “data” directory, and click properties, and select the security tab.  Give the local Postgres user (or for advanced users the user account Postgres uses) and give full permission on that direct and all child objects (sometimes you need to reset the child permission inheritance).  Click ok on the properties window.

Step 4:

Open regedit.exe by clicking on the start button and typing “rededit” in either the search box, or start->run box (depending on whether your using Vista or XP) and navigate to the “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-some version” branch. Double click on “ImagePath” and change the directory after the “–D” option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.

Step 5:

Close regedit and go back to Computer Management and restart the Postgres service. Close Computer management.

After  completeing these steps, and assuming something hasn’t gone wrong, you should now have Postgres running from the new location.

(Reference: http://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows)