Installing a database server such as MySQL or PostgreSQL is not hard really. Being asked to move a database that already exists, on an OS you're not intimately familiar with, where it's in use on a high-traffic web site, and your knowledge of the database server is limited can be intimidating to say the least. I'm speaking of a PostgreSQL 8.1 running on a MAC OS X X-server in this case. After scrounging the web for several days I found information on the PostgreSQL database server to be less than I had hoped. There is plenty of documentation, user groups, and many hits on the web, but it just doesn't quite tell the whole story on what changes need to be made and it's been difficult to find the specific information needed to make the change. I've located 2 3 methods to accomplish the task of moving the data store (location of the database files). The Backup Method and the Move Method. Hopefully they are self explanatory, but here's a synopsis;

Backup Method

1) Use pg_pump or pg_dumpall to backup the databases

2) Create the new databases in PostgreSQL

3) Restore backups

Move Method

1) Copy/Move the database store

2) Edit configuration files (i.e.; $PGDATA) There is one variable in PostgreSQL that needs to be changed regardless of the method chosen; $PGDATA. The files I've identified as needing to be changed are;

• - /usr/local/pgsql/data/postmaster.pid This file is modified at startup. Change is not required.
• - /Library/StartupItems/PostgreSQL

We're dealing with a fairly large database in particular (53GB). In a high traffic environment, minimizing down-time is of the utmost importance. Consequently my colleague and I have opted to be conservative and prep for both methods. Backup and restore could take several hours depending on how things go. Moving the data store seems to be the fastest method, so we've decided to try that first. An even better/faster solution:

Symlink Method

1. Stop the PostgreSQL server.
2. Copy/move the data-store to the new location and reset permissions to match original.
3. Rename existing /data directory.
4. Create symlink to new/data directory.
5. Restart the PostgreSQL server.

• Add New
• Search
• RSS

Comments (0)

Write comment

Your Contact Details:

Name:

Email: do not notifynotify Gravatar enabled

Website:

Comment:

Title:

UBBCode:         -color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge

Message:

Security

Please input the anti-spam code that you can read in the image.