Thread: Restoring from filesystem backup
Hello to everybody. I just have a couple of newbie questions. I'm trying to restore our databases from a file system backup. The backups are done with rsync, the postgresql service is stopped before backups are done on the server. The system is Fedora Core 4 with Postgresql 8.0.3. Now back to restoring. I tried the following on a development box :) 1. stop postgresql service 2. mv /home/postgres/data /home/postgres/data.old 3. rsync over desired data/ folder from an external drive 4. start postgresql service The last step failed with this error from the logs: postmaster: could not find the database system Expected to find it in the directory "/home/postgres/data", but could not open file "/home/postgres/data/global/pg_control": No such file or directory Sure enough pg_control data was missing, actually /home/postgres/data/global was empty. So I removed the 'bad' data/ directory and replaced it with a previous day's backup, and everything restored nicely. My questions are (1) what am I doing wrong that the filesystem backups would sometimes have an empty /home/postgres/data/global directory, thereby not permitting me to restore from it, and (2) what is the preferred way to restore from a file system backup. Any help, even a shove to the right direction, would be greatly appreciated. Thank you and have a pleasant day. Dianne
I'm no expert, but I did just accomplish a restore from backup; so at
least I can help you with your second question.
pg_dump is the animal you'll want to use to create your backup. I
use it wrapped in a script that automatically timestamps and names
the output appropriately. Args looks like this:
pg_dump -Ft -b -v the_db_name > the_output_file.tar
pg_restore restores the database. i.e.:
pg_restore -d the_db_name the_output_file.tar
Hope this helps -
Scott
On Nov 10, 2005, at 2:41 PM, Dianne Yumul wrote:
> Hello to everybody.
>
> I just have a couple of newbie questions. I'm trying to restore our
> databases from a file system backup. The backups are done with
> rsync, the postgresql service is stopped before backups are done on
> the server. The system is Fedora Core 4 with Postgresql 8.0.3.
>
> Now back to restoring. I tried the following on a development box :)
> 1. stop postgresql service
> 2. mv /home/postgres/data /home/postgres/data.old
> 3. rsync over desired data/ folder from an external drive
> 4. start postgresql service
> The last step failed with this error from the logs:
> postmaster: could not find the database system
> Expected to find it in the directory "/home/postgres/data", but
> could not open file "/home/postgres/data/global/pg_control":
> No such file or directory
> Sure enough pg_control data was missing, actually /home/postgres/
> data/global was empty. So I removed the 'bad' data/ directory and
> replaced it with a previous day's backup, and everything restored
> nicely.
>
> My questions are (1) what am I doing wrong that the filesystem
> backups would sometimes have an empty /home/postgres/data/global
> directory, thereby not permitting me to restore from it, and (2)
> what is the preferred way to restore from a file system backup.
>
> Any help, even a shove to the right direction, would be greatly
> appreciated. Thank you and have a pleasant day.
>
> Dianne
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Thanks for the quick response. We do both pg_dump and file system backups, and I agree that pg_dump is the way to go. I thought I'd practice restoring from a filesystem backup though since I've never done that before. Thank you. On Nov 10, 2005, at 6:07 PM, Scott Frankel wrote: > > I'm no expert, but I did just accomplish a restore from backup; so at > least I can help you with your second question. > > pg_dump is the animal you'll want to use to create your backup. I use > it wrapped in a script that automatically timestamps and names the > output appropriately. Args looks like this: > > pg_dump -Ft -b -v the_db_name > the_output_file.tar > > pg_restore restores the database. i.e.: > > pg_restore -d the_db_name the_output_file.tar > > Hope this helps - > Scott > > > > On Nov 10, 2005, at 2:41 PM, Dianne Yumul wrote: > >> Hello to everybody. >> >> I just have a couple of newbie questions. I'm trying to restore our >> databases from a file system backup. The backups are done with rsync, >> the postgresql service is stopped before backups are done on the >> server. The system is Fedora Core 4 with Postgresql 8.0.3. >> >> Now back to restoring. I tried the following on a development box :) >> 1. stop postgresql service >> 2. mv /home/postgres/data /home/postgres/data.old >> 3. rsync over desired data/ folder from an external drive >> 4. start postgresql service >> The last step failed with this error from the logs: >> postmaster: could not find the database system >> Expected to find it in the directory "/home/postgres/data", but >> could not open file "/home/postgres/data/global/pg_control": >> No such file or directory >> Sure enough pg_control data was missing, actually >> /home/postgres/data/global was empty. So I removed the 'bad' data/ >> directory and replaced it with a previous day's backup, and >> everything restored nicely. >> >> My questions are (1) what am I doing wrong that the filesystem >> backups would sometimes have an empty /home/postgres/data/global >> directory, thereby not permitting me to restore from it, and (2) what >> is the preferred way to restore from a file system backup. >> >> Any help, even a shove to the right direction, would be greatly >> appreciated. Thank you and have a pleasant day. >> >> Dianne >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Thanks for the response. > does the global directory exist on the drive you are syncing from? If > so ... > then it's a problem with your rsync. > > check the options you are using with the rsync command. > check the rsync configurations in /etc/rsyncd.conf on the drive. > what user are you running the rsync daemon, and rsync command as? The global directory was not on the backup I was syncing from. The option we use with rysnc is just -a. We also don't run the rsync daemon (is this ok?) just the command. I checked the other backups and they all have the global directory except for this specific one. Looking further, I think something went wrong on that backup because some directories not related to postgres, not just global/, are empty. So now I'm thinking it might be hardware (the portable external drive is about 3 years old), but I will have to wait and see if the problem persists. Sorry for making noise and thank you very much for your help. Dianne On Nov 10, 2005, at 6:17 PM, Andrew J. Kopciuch wrote: >> My questions are (1) what am I doing wrong that the filesystem backups >> would sometimes have an empty /home/postgres/data/global directory, >> thereby not permitting me to restore from it, and (2) what is the >> preferred way to restore from a file system backup. >> > > does the global directory exist on the drive you are syncing from? If > so ... > then it's a problem with your rsync. > > check the options you are using with the rsync command. > check the rsync configurations in /etc/rsyncd.conf on the drive. > what user are you running the rsync daemon, and rsync command as? > > That should point you in the direction to solve your problem. > > > > Andy >