1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • pg_dump - how to restore data?

    From Magnus Warker@21:1/5 to All on Wed Nov 9 17:35:01 2016
    Hello,

    I make backups like this:

    pg_dump -a -w mydb > mydb.sql

    Well, this doesn't create SQL statements, but statements like this:

    COPY table1 (atr1,atr2,atr3) FROM stdin;
    val1 val2 val3
    val1 val2 val3
    val1 val2 val3

    When I try to restore it with pg_restore, I get:

    pg_restore: [archiver] input file appears to be a text format dump.
    Please use psql.

    However, when I use cat mydb.sql | psql mydb, I get a lot of errors,
    because it's no real SQL in the file...

    How can I restore my dump?

    Thanks
    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Harald Wenninger@21:1/5 to All on Thu Nov 10 14:08:45 2016
    * Magnus Warker tat kund und zu wissen:

    I make backups like this:
    pg_dump -a -w mydb > mydb.sql

    That’s a data only dump, i.e. you have to connect to a database contaong
    the empty database tables etc. of mydb.
    Why do you not create a full database dump?

    Well, this doesn't create SQL statements, but statements like this:
    COPY table1 (atr1,atr2,atr3) FROM stdin;
    val1 val2 val3
    val1 val2 val3
    val1 val2 val3

    It’s valid SQL for a postgresql database. And as you only requested the
    data, you get only the data …

    However, when I use cat mydb.sql | psql mydb, I get a lot of errors,
    because it's no real SQL in the file...

    How can I restore my dump?

    Connect to a database containing the empty database schema of mydb:

    $ psql -X mydb_new < mydb.sql

    Or just create a full database dump and restore into a completly empty
    new database:

    $ pg_dump mydb > mydb.sql
    $ psql -X mydb_new < mydb.sql

    Regards,
    Harald

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Magnus Warker@21:1/5 to Harald Wenninger on Thu Nov 10 19:26:13 2016
    Hi Harald!

    On 11/10/2016 03:08 PM, Harald Wenninger wrote:
    * Magnus Warker tat kund und zu wissen:

    I make backups like this:
    pg_dump -a -w mydb > mydb.sql

    That’s a data only dump, i.e. you have to connect to a database contaong the empty database tables etc. of mydb.
    Why do you not create a full database dump?

    Because it's my app that creates the empty database.

    Well, this doesn't create SQL statements, but statements like this:
    COPY table1 (atr1,atr2,atr3) FROM stdin;
    val1 val2 val3
    val1 val2 val3
    val1 val2 val3

    It’s valid SQL for a postgresql database. And as you only requested the data, you get only the data …

    Then psql should not alert syntax errors... #-)

    How can I restore my dump?
    Connect to a database containing the empty database schema of mydb:
    $ psql -X mydb_new < mydb.sql

    Ok, this works, thank you!

    Magnus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Robert Klemme@21:1/5 to Magnus Warker on Fri Nov 11 23:39:18 2016
    On 10.11.2016 19:26, Magnus Warker wrote:

    On 11/10/2016 03:08 PM, Harald Wenninger wrote:

    It’s valid SQL for a postgresql database. And as you only requested the
    data, you get only the data …

    Then psql should not alert syntax errors... #-)

    You never showed the errors. So how is anybody supposed to reply to that?

    Cheers

    robet

    --
    remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)