Re: Restoring from filesystem backup

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
>