pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump/pg_restore
12+ messages / 8 participants
[nested] [flat]
* pg_dump/pg_restore
@ 2004-01-12 11:45 Alexey Bobkov <bax@sunet.ru>
2004-01-12 14:46 ` Re: pg_dump/pg_restore Tom Lane <tgl@sss.pgh.pa.us>
2004-01-12 16:17 ` Re: pg_dump/pg_restore Sai Hertz And Control Systems <sank89@sancharnet.in>
0 siblings, 2 replies; 12+ messages in thread
From: Alexey Bobkov @ 2004-01-12 11:45 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL 7.3.2
I have been dumping my database with next options:
pg_dump -f /path_to_database/backup/db_backup.file -Z 9 database_name
and get db_backup.file file.
Then a try to restore my data:
pg_restore /path_to_database/backup/db_backup.file
and get next error
pg_restore: [archiver] input file does not appear to be a valid archive
I think it's because in pg_dump, I not set -F c option :(
How I can restore this broken file?
Thanks
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2004-01-12 11:45 pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
@ 2004-01-12 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-12 15:01 ` Re: pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-01-12 14:46 UTC (permalink / raw)
To: Alexey Bobkov <bax@sunet.ru>; +Cc: pgsql-admin
Alexey Bobkov <bax@sunet.ru> writes:
> I have been dumping my database with next options:
> pg_dump -f /path_to_database/backup/db_backup.file -Z 9 database_name
> and get db_backup.file file.
This is giving you a plain SQL-script dump file. To restore, feed it
into psql.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2004-01-12 11:45 pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
2004-01-12 14:46 ` Re: pg_dump/pg_restore Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-01-12 15:01 ` Alexey Bobkov <bax@sunet.ru>
0 siblings, 0 replies; 12+ messages in thread
From: Alexey Bobkov @ 2004-01-12 15:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Tom Lane wrote:
> Alexey Bobkov <bax@sunet.ru> writes:
>
>>I have been dumping my database with next options:
>>pg_dump -f /path_to_database/backup/db_backup.file -Z 9 database_name
>>and get db_backup.file file.
>
>
> This is giving you a plain SQL-script dump file. To restore, feed it
> into psql.
No :)
this is giving me gzip compressed file
I found solution for my problem.
This is step by step instruction for me :)
mv db_backup.file db_backup.file.gz
gzip -g db_backup.file.gz
:)
thanks
--
Alexey Bobkov
e-Style ISP
tel/fax +7 095 7969797
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2004-01-12 11:45 pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
@ 2004-01-12 16:17 ` Sai Hertz And Control Systems <sank89@sancharnet.in>
2004-01-12 16:29 ` Re: pg_dump/pg_restore John Siracusa <siracusa@mindspring.com>
1 sibling, 1 reply; 12+ messages in thread
From: Sai Hertz And Control Systems @ 2004-01-12 16:17 UTC (permalink / raw)
To: Alexey Bobkov <bax@sunet.ru>; +Cc: pgsql-admin
Dear Alexey Bobkov ,
>
> Then a try to restore my data:
> pg_restore /path_to_database/backup/db_backup.file
> and get next error
> pg_restore: [archiver] input file does not appear to be a valid archive
Use psql instead with command
psql -U username databasename -f yourdb_file.sql
Yes you will have to edit the file a bit.
Drawbacks are :
triggers will go of on all insert and will be stumbling block in case to
dump you may use the following command in future
pg_dump --disable-triggers -U <user_name> -a -d -b -D -Fc -Z 9 -f
<filername.tar.gz> <dbname>
and to restore use
pg_restore --disable-triggers -U <user_name> -d <dbname>
<backupfilename.tar.gz>
Regards,
Vishal Kashyap
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2004-01-12 11:45 pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
2004-01-12 16:17 ` Re: pg_dump/pg_restore Sai Hertz And Control Systems <sank89@sancharnet.in>
@ 2004-01-12 16:29 ` John Siracusa <siracusa@mindspring.com>
0 siblings, 0 replies; 12+ messages in thread
From: John Siracusa @ 2004-01-12 16:29 UTC (permalink / raw)
To: pgsql-admin
On 1/12/04 11:17 AM, Sai Hertz And Control Systems wrote:
> Dear Alexey Bobkov ,
>> Then a try to restore my data:
>> pg_restore /path_to_database/backup/db_backup.file
>> and get next error
>> pg_restore: [archiver] input file does not appear to be a valid archive
>
> Use psql instead with command
> psql -U username databasename -f yourdb_file.sql
> Yes you will have to edit the file a bit.
> Drawbacks are :
> triggers will go of on all insert and will be stumbling block
Is this true when dumping using pg_dumpall and restoring by piping to psql?
Are triggers going off during the restore? I don't see a --disable-triggers
option to pg_dumpall.
-John
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PG_DUMP / PG_RESTORE
@ 2004-05-25 16:01 Laurens Wagemakers <l.wagemakers@truston.com>
2004-05-25 18:45 ` Re: PG_DUMP / PG_RESTORE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Laurens Wagemakers @ 2004-05-25 16:01 UTC (permalink / raw)
To: pgsql-admin
Hi all,
Running postgresql 7.1.3 on Solaris 9:
Restoring a database from a RH 7.3 Postgresql 7.1 version
Get the message:
CREATE SEQUENCE "live_autoincrement" start 1 increment 1 maxvalue
92233720368547
75807 minvalue 1 cache 1;
ERROR: parser: parse error at or near "9223372036854775807"
(set query logging on)
Looked at a message from Tom Lane on Date: Tue, 06 Aug 2002 23:21:14 -0400
however no answer for it.
Any solutions,
Regards,
Laurens
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PG_DUMP / PG_RESTORE
2004-05-25 16:01 Re: PG_DUMP / PG_RESTORE Laurens Wagemakers <l.wagemakers@truston.com>
@ 2004-05-25 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-05-25 18:45 UTC (permalink / raw)
To: Laurens Wagemakers <l.wagemakers@truston.com>; +Cc: pgsql-admin
Laurens Wagemakers <l.wagemakers@truston.com> writes:
> Running postgresql 7.1.3 on Solaris 9:
> Restoring a database from a RH 7.3 Postgresql 7.1 version
You generally aren't going to be able to restore a dump into an older
version without some manual labor.
> CREATE SEQUENCE "live_autoincrement" start 1 increment 1 maxvalue
> 92233720368547
> 75807 minvalue 1 cache 1;
> ERROR: parser: parse error at or near "9223372036854775807"
In this particular case the issue is that 7.1 didn't have 64-bit
sequence values. (No, I don't think your RH database is 7.1 ...)
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PG_DUMP / PG_RESTORE
@ 2004-05-27 05:31 Laurens Wagemakers <l.wagemakers@truston.com>
0 siblings, 0 replies; 12+ messages in thread
From: Laurens Wagemakers @ 2004-05-27 05:31 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Again you got it right although my customer was sure he had 7.1 running I
asked him to run psql -V which resolved version 7.2.1
Thanx,
Laurens
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 25, 2004 8:46 PM
To: Laurens Wagemakers
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] PG_DUMP / PG_RESTORE
Laurens Wagemakers <l.wagemakers@truston.com> writes:
> Running postgresql 7.1.3 on Solaris 9:
> Restoring a database from a RH 7.3 Postgresql 7.1 version
You generally aren't going to be able to restore a dump into an older
version without some manual labor.
> CREATE SEQUENCE "live_autoincrement" start 1 increment 1 maxvalue
> 92233720368547
> 75807 minvalue 1 cache 1;
> ERROR: parser: parse error at or near "9223372036854775807"
In this particular case the issue is that 7.1 didn't have 64-bit
sequence values. (No, I don't think your RH database is 7.1 ...)
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* pg_dump/pg_restore
@ 2008-04-08 19:52 Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
2008-04-08 20:44 ` Re: pg_dump/pg_restore Tino Schwarze <postgresql@tisc.de>
2008-04-08 21:30 ` Re: pg_dump/pg_restore Tom Lane <tgl@sss.pgh.pa.us>
2008-04-09 13:14 ` Re: pg_dump/pg_restore Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 3 replies; 12+ messages in thread
From: Sergio Gabriel Rodriguez @ 2008-04-08 19:52 UTC (permalink / raw)
To: pgsql-admin
Hi for all, first sorry for my english is not good.
I'm using postgresql 7.4, I have a problem with pg_dump/pg_restore. I
want to back and restore only one schema from my db (I did it), but
when I restore it, postgresql found errors in some views, this ones
needs of others which can't be found because the script will generate
them later. How can I make an orderer sql dump???
I hope you can understand me :-).
Sergio Rodríguez (Argentina)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2008-04-08 19:52 pg_dump/pg_restore Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
@ 2008-04-08 20:44 ` Tino Schwarze <postgresql@tisc.de>
2 siblings, 0 replies; 12+ messages in thread
From: Tino Schwarze @ 2008-04-08 20:44 UTC (permalink / raw)
To: pgsql-admin
On Tue, Apr 08, 2008 at 04:52:23PM -0300, Sergio Gabriel Rodriguez wrote:
> I'm using postgresql 7.4, I have a problem with pg_dump/pg_restore. I
> want to back and restore only one schema from my db (I did it), but
> when I restore it, postgresql found errors in some views, this ones
> needs of others which can't be found because the script will generate
> them later. How can I make an orderer sql dump???
A quick fix would be to just restore the dump multiple times -
everything which already exists will be skipped, the rest will be
loaded. Repeat until everything is fine. ;-)
Hope that helps,
Tino.
--
„Es gibt keinen Weg zum Frieden. Der Frieden ist der Weg.” (Mahatma Gandhi)
www.craniosacralzentrum.de
www.forteego.de
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2008-04-08 19:52 pg_dump/pg_restore Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
@ 2008-04-08 21:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2008-04-08 21:30 UTC (permalink / raw)
To: Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>; +Cc: pgsql-admin
"Sergio Gabriel Rodriguez" <sgrodriguez@gmail.com> writes:
> I'm using postgresql 7.4, I have a problem with pg_dump/pg_restore. I
> want to back and restore only one schema from my db (I did it), but
> when I restore it, postgresql found errors in some views, this ones
> needs of others which can't be found because the script will generate
> them later. How can I make an orderer sql dump???
Switch to a newer PG version. If memory serves, 8.0 was the first
version in which pg_dump paid attention to dependencies rather than just
the order of original creation of views. In 7.x it wasn't hard to
cause problems by doing CREATE OR REPLACE VIEW.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_dump/pg_restore
2008-04-08 19:52 pg_dump/pg_restore Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
@ 2008-04-09 13:14 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 12+ messages in thread
From: Andrew Sullivan @ 2008-04-09 13:14 UTC (permalink / raw)
To: pgsql-admin
On Tue, Apr 08, 2008 at 04:52:23PM -0300, Sergio Gabriel Rodriguez wrote:
> needs of others which can't be found because the script will generate
> them later. How can I make an orderer sql dump???
Use the custom format, use pg_restore to get the catalogue, and then edit
that catalogue to re-order the restore..
A
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-04-09 13:14 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-12 11:45 pg_dump/pg_restore Alexey Bobkov <bax@sunet.ru>
2004-01-12 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-12 15:01 ` Alexey Bobkov <bax@sunet.ru>
2004-01-12 16:17 ` Sai Hertz And Control Systems <sank89@sancharnet.in>
2004-01-12 16:29 ` John Siracusa <siracusa@mindspring.com>
2004-05-25 16:01 Re: PG_DUMP / PG_RESTORE Laurens Wagemakers <l.wagemakers@truston.com>
2004-05-25 18:45 ` Re: PG_DUMP / PG_RESTORE Tom Lane <tgl@sss.pgh.pa.us>
2004-05-27 05:31 Re: PG_DUMP / PG_RESTORE Laurens Wagemakers <l.wagemakers@truston.com>
2008-04-08 19:52 pg_dump/pg_restore Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
2008-04-08 20:44 ` Tino Schwarze <postgresql@tisc.de>
2008-04-08 21:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-09 13:14 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox