Re: Restoring from filesystem backup

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
>