Re: [SL] Restoring from a pg_dump

[George O. <geo...@ya...>]

On Friday 06 April 2007 00:18, R Pennington wrote:
> On Monday 02 April 2007 6:01 pm, Glen Morris wrote:
> > I have a script that backs up my psql databases each night (SL 2.6.27).
> > For the first time I tried to restore (because of failed 2.8.0 upgrade)
> > from one but it failed. I used this command to create the backup:
> >
> >     pg_dump -U sql-ledger dea > /spare/backup/postgresql/pgsql-dea_`date
> >     +%G%m%d`.sql
> >
> > And then tried to restore as per the manual with:
> >
> >     psql -U sql-ledger dea < pgsql-dea_20070402.sql
> >
> >
> > gives lots of:
> >
> >     invalid command \N
> >     invalid command \N
> >     invalid command \N
> >     invalid command \N
> >     ERROR:  syntax error at or near "10078" at character 1
> >     LINE 1: 10078 johne
> >
> > Restoring from a SL backup works fine but I don't have one that's as
> > current as the pg_dump.
> >
> > Regards Glen
>

When I was using a backup file created from SL I would change for postgres 
user and then go:

psql \i backup.sql mydatabase

If I was getting any error messages either from sql traceback or after login I 
would copy those on clipboard resolve and run the command again. (for example 
I had existing tables that I had to delete) I also found that I had to   
delete my database before restoring otherwise I would get double entries.

Normally how ever I am running automatic backups from cron for the whole 
system and also for databases. Something like this:
0 4 * * *	postgres pg_dump --format=t mydatabase > backup.tgz
20 4 * * *	postgres   pg_dumpall > alldata.sql

Typically I would restore from alldata.sql with:

psql template1 -f alldata.sql

that resores your whole database structure.


-- 
George
AUSTRALIA
http://www.okstudio.com.au