sql-ledger-users

[SL] Restoring from a pg_dump

[Glen M. <gl...@au...>]

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


-- 
Glen Morris: Arachnoid Australia
613 90175780 Melbourne | 612 90164032 Sydney
613 98961811 VoIP | Mbl 0419299140
Skype=glenmo. PO Box 214, Healesville VIC 3777, Australia

Re: [SL] Restoring from a pg_dump

[R P. <arc...@mt...>]

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
> 
> 
Hi Glen

use pg_restore to restore the data.
pg_dump and pg_restore are designed to be used together.

"psql -U sql-ledger dea < pgsql-dea_20070402.sql" works for restoring SL backups

Cheers,
Roy

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