Migrating PostgreSQL Database
Hello experts :) PostgreSQL
We have and old server running postgreSQL 7.4 and CentOS 4.4 (pretty old, its been up since about 2005) with its hard drive at about 3% free. We have a new server at hand (Ubuntu 12.04, PostgreSQL isn't installed yet) and I would like to ask your advice on how can we migrate the database from the old with no downtime or minimal downtime as possible.
Any steps on how we can migrate safely and immediately?
Thank you in advance.
Re: Migrating PostgreSQL Database
moved to server platforms
Re: Migrating PostgreSQL Database
Re: Migrating PostgreSQL Database
To move an entire server you should use pg_dumpall. pg_dump is designed to back up a single database.
If you're not running this utility to back up your database every night, I strongly recommend writing a script to do so. I once lost a PG database with no backup and now write backups with pg_dumpall every night and suck them to a server in my office with rsync.
Re: Migrating PostgreSQL Database
Quote:
Originally Posted by
CharlesA
Thanks! I'll try it first on a test server and see how it works. While using this command, will the new database from version 7.4 usable to the new version 9.1?
Re: Migrating PostgreSQL Database
Quote:
Originally Posted by
SeijiSensei
To move an entire server you should use pg_dumpall. pg_dump is designed to back up a single database.
If you're not running this utility to back up your database every night, I strongly recommend writing a script to do so. I once lost a PG database with no backup and now write backups with pg_dumpall every night and suck them to a server in my office with rsync.
Thanks! We do have a script to backup the old server but the backup copy hard drive is also full because they are identical hard drives. By the way, is there any difference between pg_dumpall and pg_dump databasename if you only have one database?
Re: Migrating PostgreSQL Database
Quote:
Originally Posted by
nerdtron
Thanks! We do have a script to backup the old server but the backup copy hard drive is also full because they are identical hard drives. By the way, is there any difference between pg_dumpall and pg_dump databasename if you only have one database?
There shouldn't be, but I'm used to MySQL, myself.
Re: Migrating PostgreSQL Database
No, I don't think so. I dump and restore specific databases all the time, like when I'm working on a site. I use pg_dump to create the backup, then create a new empty database for development with createdb and populate it from the backup with "psql -U username dbname < /path/to/backup.sql".
I haven't encountered any problems moving a backup from an older version of PostgreSQL to a newer one. Most of the information in the backup uses pretty stock commands like CREATE TABLE, CREATE INDEX, and COPY.