#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    1
    Rep Power
    0

    Restore/use databse from data file backup


    I need to restore some data from yesterday into my postgres database. I only have a filesystem backup each night so I have the full contents of /var/lib/pgsql which contains data, base, global etc. etc.

    How do I copy my backup database folder into my currently running or maybe a spare server so that I can query yesterdays data to export back into the current db?

    I think my databse is /var/lib/pgsql/data/base/156814 and my backup is on /mnt/buffalo/daily.0/var/lib/pgsql/data/base/156814

    Please please can someone help it's pretty urgent!

    Thanks in advance
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally Posted by tariqf
    I need to restore some data from yesterday into my postgres database. I only have a filesystem backup each night so I have the full contents of /var/lib/pgsql which contains data, base, global etc. etc.

    How do I copy my backup database folder into my currently running or maybe a spare server so that I can query yesterdays data to export back into the current db?
    You *never* copy your backup data directory into your currently running PostgreSQL directory. That's a perfect path to disaster. There is one and only one way that might (might) help you:

    1. Stop the postgreSQL service
    2. Make a complete backup of your *existing* postgreSQL data folder. Be very careful that this is a full backup.
    3. Remove your existing data folder, and copy your backup folder back into place. This means your *complete* backup of your data directory, not just the particular location that you think might be your database (/mnt/buffalo/daily.0/var/lib/pgsql/data/base/156814)
    4. Start PostgreSQL running again, and do a complete pg_dump of the database you are trying to rescue.*
    5. Stop the PostgreSQL service again
    6. Remove the older version of your data directory, and restore your current copy into place.
    7. Start PostgreSQL and then begin trying to merge your pg_dump records with your current copy of the database.

    *Note that I can't promise you the the backed up data directory has perfect data integrity. Simply copying your data folder while PostgreSQL is running is a bad practice and can result in corrupt backups. The only right way to backup PostgreSQL is to run pg_dump regularly.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo