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)

* 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)

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)

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)