Results 1 to 8 of 8

Thread: Migrating PostgreSQL Database

  1. #1
    Join Date
    Aug 2009
    Location
    Makati City, Philippines
    Beans
    826
    Distro
    Xubuntu 14.04 Trusty Tahr

    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.
    The Philippine Team
    The best Ubuntu derivative: Linux Mint
    Relax. Head to the Community Cafe.

  2. #2
    Join Date
    Nov 2008
    Location
    BSAA Headquarters
    Beans
    Hidden!
    Distro
    Ubuntu Development Release

    Re: Migrating PostgreSQL Database

    moved to server platforms
    Ubuntu Forums Moderation Staff || SandyDNET
    Twitter: @CatchesAStar | Last.fm
    Ubuntu Membership via Forum Contributions

  3. #3
    Join Date
    Oct 2009
    Beans
    Hidden!
    Distro
    Ubuntu 12.04 Precise Pangolin

    Re: Migrating PostgreSQL Database

    Come to #ubuntuforums! We have cookies! | Basic Ubuntu Security Guide

    Tomorrow's an illusion and yesterday's a dream, today is a solution...

  4. #4
    Join Date
    Nov 2008
    Location
    Metro Boston
    Beans
    8,724
    Distro
    Kubuntu 14.04 Trusty Tahr

    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.
    If you ask for help, please have the courtesy to check for responses and thank the people who helped you.

    Blog · Linode System Administration Guides · Android Apps for Ubuntu Users

  5. #5
    Join Date
    Aug 2009
    Location
    Makati City, Philippines
    Beans
    826
    Distro
    Xubuntu 14.04 Trusty Tahr

    Re: Migrating PostgreSQL Database

    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?
    Last edited by nerdtron; March 25th, 2013 at 02:58 AM.
    The Philippine Team
    The best Ubuntu derivative: Linux Mint
    Relax. Head to the Community Cafe.

  6. #6
    Join Date
    Aug 2009
    Location
    Makati City, Philippines
    Beans
    826
    Distro
    Xubuntu 14.04 Trusty Tahr

    Re: Migrating PostgreSQL Database

    Quote Originally Posted by SeijiSensei View Post
    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?
    The Philippine Team
    The best Ubuntu derivative: Linux Mint
    Relax. Head to the Community Cafe.

  7. #7
    Join Date
    Oct 2009
    Beans
    Hidden!
    Distro
    Ubuntu 12.04 Precise Pangolin

    Re: Migrating PostgreSQL Database

    Quote Originally Posted by nerdtron View Post
    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.
    Come to #ubuntuforums! We have cookies! | Basic Ubuntu Security Guide

    Tomorrow's an illusion and yesterday's a dream, today is a solution...

  8. #8
    Join Date
    Nov 2008
    Location
    Metro Boston
    Beans
    8,724
    Distro
    Kubuntu 14.04 Trusty Tahr

    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.
    If you ask for help, please have the courtesy to check for responses and thank the people who helped you.

    Blog · Linode System Administration Guides · Android Apps for Ubuntu Users

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •