[BUGS] PG_restore
Hi Is it possible to restore pg db with the same oids
[BUGS] pg_restore
Hi,I have problem.I want to restore only data in database.In pgAdmin I was backup only data from old database and now i have backup file backup.sql.In terminale I want restore only data in new database and I using command pg_restore -i -h localhost -p 5432 -U postgres -d payroll --data-only -v /home/postgres/backup.sql,but i get an error:pg_restore:[archiver] input file appears to be a text format dump.Please use psql Thanks in advance for your suggestions.
Re: [BUGS] pg_restore
Branka Stancic wrote: I using command pg_restore get an error: pg_restore: [archiver] input file appears to be a text format dump. Please use psql pg_restore is not used for restoring text files; psql is. If you want to use pg_restore you should use -Fc when you run pg_dump. At this point, you need to run the script file in through psql. If it contains steps you don't want to execute, you will need to copy and edit the file to leave just the portions you want to run. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore
On 12/5/2012 4:40 AM, Branka Stancic wrote: I have problem. I want to restore only data in database. In pgAdmin I was backup only data from old database and now i have backup file backup.sql. In terminale I want restore only data in new database and I using command pg_restore -i -h localhost -p 5432 -U postgres -d payroll --data-only -v /home/postgres/backup.sql, but i get an error: pg_restore:[archiver] input file appears to be a text format dump.Please use psql pg_restore is only for binary dumps such as are generated by pg_dump -Fc plain text dumps are restored with psql -f filename.sql if you dumped just the data, then thats all it will restore (data only mean it won't create the tables and such), but you probably should truncate the existing tables first if there's old data in them. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
Hi Magnus Heiki, The above problem solved when I have connected 8.1 server through PgAdmin III OF 9.1 version and restored. So, I think there was some bug fix related to the above problem in PgSql 9.1 pg_restore.exe. This is for your reference and information. Thanks and regards, Satheesan K Nair India - Original Message - From: Magnus Hagander mag...@hagander.net To: Heikki Linnakangas heikki.linnakan...@enterprisedb.com Cc: Satheesan K Nair supp...@shakthiinnovators.com; pgsql-bugs@postgresql.org Sent: Monday, October 31, 2011 4:57 PM Subject: Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument On Mon, Oct 31, 2011 at 12:03, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 31.10.2011 12:12, Satheesan K Nair wrote: Our Operating System is Windows XP. Should we need to do any changes in any kind of configurations in Windows XP or PostgreSql?? According to the commit logs, there was a win32-related fix to this on 8.2, and some further MinGW-related changes in 8.4. Regardless of this issue, I'd suggest that you upgrade to the latest version, 9.1.1. 8.1 isn't supported anymore, and 8.2 is just about to reach EOL by the end of December (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy). Note that 8.1 was EOL'ed on Windows specifically even earlier than it was in general. We are trying to restore the data base backup in 8.1 version through 9.1 PGAdmin III. PGDump ran through Fedora Linux 10 (Data Base Server) OS. Is there any other chance to give the above error? I meant something like, 1.pgdump not completed the dumping properly, 2.the Backup file is not saved/copied properly, etc?? I believe the dump is OK, but restoring it doesn't work on version 8.1 on Windows. A workaround could be to run pg_restore on a 64-bit Unix box, talking to postgresql server on Windows. That should work around the issue - since the bug is in pg_dump/pg_restore and not in the backend. But I second and thid Heikkis recommendation that you need to get off version 8.1 and onto something more recent as quickly as possible. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
Dear Supporter, We are trying to restore a 3.2GB sized POstgreSql 8.1 Data base. When it is reaching a particular table restoration, giving error as pg_restore: [custom archiver] error during file seek: Invalid argument and exiting the restoration. Then when we have googled it, we found that PostgreSql will not support a Backup file of more than 2 GB. We would like to know that any of later Versions of the PostgreSql have been got fixed this problem? Awaiting for your reply. Satheesan K Nair India
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
On 31.10.2011 07:55, Satheesan K Nair wrote: We are trying to restore a 3.2GB sized POstgreSql 8.1 Data base. When it is reaching a particular table restoration, giving error as pg_restore: [custom archiver] error during file seek: Invalid argument and exiting the restoration. Then when we have googled it, we found that PostgreSql will not support a Backup file of more than 2 GB. We would like to know that any of later Versions of the PostgreSql have been got fixed this problem? I belive PostgreSQL has been able to handle that since version 7.3, if the operating system support is there. What operating system and version do you use? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
On 31.10.2011 12:12, Satheesan K Nair wrote: Our Operating System is Windows XP. Should we need to do any changes in any kind of configurations in Windows XP or PostgreSql?? According to the commit logs, there was a win32-related fix to this on 8.2, and some further MinGW-related changes in 8.4. Regardless of this issue, I'd suggest that you upgrade to the latest version, 9.1.1. 8.1 isn't supported anymore, and 8.2 is just about to reach EOL by the end of December (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy). We are trying to restore the data base backup in 8.1 version through 9.1 PGAdmin III. PGDump ran through Fedora Linux 10 (Data Base Server) OS. Is there any other chance to give the above error? I meant something like, 1.pgdump not completed the dumping properly, 2.the Backup file is not saved/copied properly, etc?? I believe the dump is OK, but restoring it doesn't work on version 8.1 on Windows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
On Mon, Oct 31, 2011 at 12:03, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 31.10.2011 12:12, Satheesan K Nair wrote: Our Operating System is Windows XP. Should we need to do any changes in any kind of configurations in Windows XP or PostgreSql?? According to the commit logs, there was a win32-related fix to this on 8.2, and some further MinGW-related changes in 8.4. Regardless of this issue, I'd suggest that you upgrade to the latest version, 9.1.1. 8.1 isn't supported anymore, and 8.2 is just about to reach EOL by the end of December (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy). Note that 8.1 was EOL'ed on Windows specifically even earlier than it was in general. We are trying to restore the data base backup in 8.1 version through 9.1 PGAdmin III. PGDump ran through Fedora Linux 10 (Data Base Server) OS. Is there any other chance to give the above error? I meant something like, 1.pgdump not completed the dumping properly, 2.the Backup file is not saved/copied properly, etc?? I believe the dump is OK, but restoring it doesn't work on version 8.1 on Windows. A workaround could be to run pg_restore on a 64-bit Unix box, talking to postgresql server on Windows. That should work around the issue - since the bug is in pg_dump/pg_restore and not in the backend. But I second and thid Heikkis recommendation that you need to get off version 8.1 and onto something more recent as quickly as possible. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
ok... let me try the same and check. Thanks for your valuable support. - Original Message - From: Heikki Linnakangas heikki.linnakan...@enterprisedb.com To: Satheesan K Nair supp...@shakthiinnovators.com Cc: pgsql-bugs@postgresql.org Sent: Monday, October 31, 2011 4:33 PM Subject: Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument On 31.10.2011 12:12, Satheesan K Nair wrote: Our Operating System is Windows XP. Should we need to do any changes in any kind of configurations in Windows XP or PostgreSql?? According to the commit logs, there was a win32-related fix to this on 8.2, and some further MinGW-related changes in 8.4. Regardless of this issue, I'd suggest that you upgrade to the latest version, 9.1.1. 8.1 isn't supported anymore, and 8.2 is just about to reach EOL by the end of December (http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy). We are trying to restore the data base backup in 8.1 version through 9.1 PGAdmin III. PGDump ran through Fedora Linux 10 (Data Base Server) OS. Is there any other chance to give the above error? I meant something like, 1.pgdump not completed the dumping properly, 2.the Backup file is not saved/copied properly, etc?? I believe the dump is OK, but restoring it doesn't work on version 8.1 on Windows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument
Hi Heiki, Our Operating System is Windows XP. Should we need to do any changes in any kind of configurations in Windows XP or PostgreSql?? We are trying to restore the data base backup in 8.1 version through 9.1 PGAdmin III. PGDump ran through Fedora Linux 10 (Data Base Server) OS. Is there any other chance to give the above error? I meant something like, 1.pgdump not completed the dumping properly, 2.the Backup file is not saved/copied properly, etc?? Satheesan K Nair India - Original Message - From: Heikki Linnakangas heikki.linnakan...@enterprisedb.com To: Satheesan K Nair supp...@shakthiinnovators.com Cc: pgsql-bugs@postgresql.org Sent: Monday, October 31, 2011 3:25 PM Subject: Re: [BUGS] pg_restore: [custom archiver] error during file seek: Invalid argument On 31.10.2011 07:55, Satheesan K Nair wrote: We are trying to restore a 3.2GB sized POstgreSql 8.1 Data base. When it is reaching a particular table restoration, giving error as pg_restore: [custom archiver] error during file seek: Invalid argument and exiting the restoration. Then when we have googled it, we found that PostgreSql will not support a Backup file of more than 2 GB. We would like to know that any of later Versions of the PostgreSql have been got fixed this problem? I belive PostgreSQL has been able to handle that since version 7.3, if the operating system support is there. What operating system and version do you use? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
I wrote: Julian Mehnle jul...@mehnle.net writes: What would removing that code entirely mean? I was wondering why it's necessary to parse the entry in the dump file at all, rather than just spit it out to PQexec as-is. There's probably a reason, but maybe we can find another way to solve whatever the real problem is. I've applied a patch along those lines. Thanks for the report! regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
Julian Mehnle jul...@mehnle.net writes: If the comment/description of a database object (table, function, etc.) ends in a backslash (which generally works fine otherwise), then pg_restore is unable to completely restore a custom-format dump of the schema. Reproduced here against HEAD. The problem seems to be that pg_backup_db.c's _sendSQLLine() contains a mini SQL lexer that is not cognizant of standard_conforming_strings. Not sure about a simple fix, and I rather wonder if we shouldn't try to remove that code entirely instead of fix it. As a temporary workaround, the SQL text file that pg_restore produces by default seems to be valid, so you could pipe that into psql. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
I'm subscribed to the pgsql-bugs list, so no need to CC me. :-) Tom Lane wrote: Reproduced here against HEAD. The problem seems to be that pg_backup_db.c's _sendSQLLine() contains a mini SQL lexer that is not cognizant of standard_conforming_strings. Oh, right, I forgot to mention I have standard_conforming_strings enabled. However, I understand that is to become the new default anyway. Not sure about a simple fix, and I rather wonder if we shouldn't try to remove that code entirely instead of fix it. What would removing that code entirely mean? As a temporary workaround, the SQL text file that pg_restore produces by default seems to be valid, so you could pipe that into psql. Hmm, right. So at least my existing dumps aren't useless. Thanks. -Julian signature.asc Description: This is a digitally signed message part.
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
Julian Mehnle jul...@mehnle.net writes: I'm subscribed to the pgsql-bugs list, so no need to CC me. :-) cc to people in the thread is the established practice on these lists. It provides a bit more robustness when the lists are busy or slow. You can set your subscription so the listserv won't send you an extra copy, I believe, but I don't know the incantation offhand. Tom Lane wrote: Not sure about a simple fix, and I rather wonder if we shouldn't try to remove that code entirely instead of fix it. What would removing that code entirely mean? I was wondering why it's necessary to parse the entry in the dump file at all, rather than just spit it out to PQexec as-is. There's probably a reason, but maybe we can find another way to solve whatever the real problem is. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
Tom Lane wrote: Julian Mehnle jul...@mehnle.net writes: I'm subscribed to the pgsql-bugs list, so no need to CC me. :-) cc to people in the thread is the established practice on these lists. It provides a bit more robustness when the lists are busy or slow. Got it. I'll try to remember that and CC others when posting, and will also not take offense when receiving extra CCs. What would removing that code entirely mean? I was wondering why it's necessary to parse the entry in the dump file at all, rather than just spit it out to PQexec as-is. There's probably a reason, but maybe we can find another way to solve whatever the real problem is. I see. Given that I'm only a Pg user, not a Pg developer, I have no opinion on this. Thanks for the explanation, though! -Julian signature.asc Description: This is a digitally signed message part.
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
Excerpts from Julian Mehnle's message of mié jul 27 13:28:21 -0400 2011: Tom Lane wrote: Julian Mehnle jul...@mehnle.net writes: I'm subscribed to the pgsql-bugs list, so no need to CC me. :-) cc to people in the thread is the established practice on these lists. It provides a bit more robustness when the lists are busy or slow. Got it. I'll try to remember that and CC others when posting, and will also not take offense when receiving extra CCs. If you're on procmail, you can get very easily use a well-known de-dupe recipe and it all works wonderfully. :0 Wh: msgid.lock | formail -D 65536 $HOME/.msgid.cache I assume similar tools can use equivalent mechanisms. Note that CCing others is customary but obviously not mandatory. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
Alvaro Herrera wrote: Note that CCing others is customary but obviously not mandatory. For the record, CCing posters who haven't explicitly requested it is frowned upon on the Debian mailing lists , but apparently those have a lower latency than the Pg ones. :-) I'll shut up now since this is drifting off topic. -Julian signature.asc Description: This is a digitally signed message part.
[BUGS] pg_restore silently chokes on object comments/descriptions ending in a backslash
I observe the following issue on PostgreSQL 9.0.4 on at least the
following platforms:
* FreeBSD 6.3 (amd64)
`uname -a`:
FreeBSD hostname 6.3-STABLE FreeBSD 6.3-STABLE #1: Fri May 30 18:11:47
PDT 2008
root@hostname:/data/obj/data/home/username/symbols/builddir_amd64/usr/src/sys/MESSAGING_GATEWAY.amd64_INSTALL
amd64
* Mac OS X 10.6.8 (i386)
`uname -a`:
Darwin joule 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT
2011; root:xnu-1504.15.3~1/RELEASE_I386 i386 i386
* semi-current Debian testing (amd64)
`uname -a`:
Linux gray 2.6.30-2-amd64 #1 SMP Mon Dec 7 05:21:45 UTC 2009 x86_64
GNU/Linux
If the comment/description of a database object (table, function, etc.)
ends in a backslash (which generally works fine otherwise), then
pg_restore is unable to completely restore a custom-format dump of the
schema. pg_restore does not complain, but silently(!) stops issuing DDL
statements to the server starting with the first COMMENT ON … statement
that would have set an object comment/description ending in a backslash.
Reproduce as follows:
$ createdb test0
$ createdb test1
$ psql -c CREATE TABLE bar (); COMMENT ON TABLE bar IS 'bar\\'; test0
COMMENT
$ psql -c CREATE TABLE foo (); COMMENT ON TABLE foo IS 'foo'; test0
COMMENT
$ pg_dump --format custom --file test0.pg_dump --schema-only test0
$ pg_restore -d test1 test0.pg_dump
$ psql -c '\dt+' test0
List of relations
Schema | Name | Type | Owner | Size | Description
+--+---++-+-
public | bar | table | julian | 0 bytes | bar\
public | foo | table | julian | 0 bytes | foo
(2 rows)
$ psql -c '\dt+' test1
List of relations
Schema | Name | Type | Owner | Size | Description
+--+---++-+-
public | bar | table | julian | 0 bytes |
(1 row)
This also happens with PostgreSQL 8.4.
To demonstrate that this is not an academic issue, these are a few
functions I have defined, and their comments:
List of functions
-[ RECORD 1
]---+
Schema | public
Name| escape_are
... : ...
Description | escape advanced regexp (ARE) special characters:
.*+?|[](){}^$\
-[ RECORD 2
]---+
Schema | public
Name| escape_control
... : ...
Description | escape control characters: \a\b\t\n\v\f\r\e\\
-[ RECORD 3
]---+
Schema | public
Name| escape_like
... : ...
Description | escape LIKE pattern special characters: %_\
I have worked around the issue by appending a space character to each of
those function descriptions. What makes the problem really bad is that it
silently renders your custom-format database dumps (which pg_dump creates
just fine) useless, which you notice only after you do a restore (without
an error being thrown) and your restored database being incomplete.
-Julian
signature.asc
Description: This is a digitally signed message part.
Re: [BUGS] pg_restore ignores -C when using a restore list -L
On 15/05/10 05:15, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: However, I think -C is a special case because it's quite un-obvious to the user that it effectively acts as a filter switch --- in fact a de-filtering switch, because the lack of -C is what filters out the DATABASE item. I'm inclined to think that we should document that the output of -l is restricted by -n and similar switches, but change the code so that -C doesn't affect -l output. Comments? +1 Was this discussed change made? Was it backported, or only applied to head? Thanks Russell -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
On Thu, Jun 10, 2010 at 3:58 AM, Russell Smith mr-r...@pws.com.au wrote: On 15/05/10 05:15, Alvaro Herrera wrote: Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: However, I think -C is a special case because it's quite un-obvious to the user that it effectively acts as a filter switch --- in fact a de-filtering switch, because the lack of -C is what filters out the DATABASE item. I'm inclined to think that we should document that the output of -l is restricted by -n and similar switches, but change the code so that -C doesn't affect -l output. Comments? +1 Was this discussed change made? Was it backported, or only applied to head? I believe this is the commit: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 It looks like the code was changed, but I don't see any doc updates. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
Robert Haas robertmh...@gmail.com writes: I believe this is the commit: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 It looks like the code was changed, but I don't see any doc updates. Eh? http://archives.postgresql.org/pgsql-committers/2010-05/msg00191.php and following entries regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
On Thu, Jun 10, 2010 at 9:39 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I believe this is the commit: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 It looks like the code was changed, but I don't see any doc updates. Eh? http://archives.postgresql.org/pgsql-committers/2010-05/msg00191.php and following entries Ah, missed that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
On 13/05/10 03:39, Tom Lane wrote: Russell Smith mr-r...@pws.com.au writes: pg_restore silently ignores the inclusion of -C when you do use a restore list. It would work as you expect if you use -C when creating the list file. The reason for this is that -C basically means don't skip the DATABASE entry. When you use -l without -C, you get a list file that doesn't include the DATABASE entry. In the last step, you need both -C and the DATABASE entry listed in the list file in order to have the CREATE DATABASE command emitted. We could possibly fix this by emitting the DATABASE entry during -l with or without -C. I'm not sure if that would have any bad side effects, but a quick test suggests that it will still skip the DATABASE entry if it's listed in the list file but -C isn't given. Of course, if you manually remove the DATABASE entry from the list file, you lose anyway. By the operation of other items (-C --data-only) passed with -l, it only produces to contents that would be restored with the other switches provided. If that's expect behavior, then the documentation of the switch is incorrect and should read something more like -l --list List the items in the archive that would be restored taking into account any other switches provided. The output of this operation can be used with the -L option to further restrict and reorder the items that are restored. Something like that as documentation for the switch would be clearer about what it's actually doing. The previous statement of listing the contents of the archive is clearly not what the switch is doing. If -l really should list the contents of the archive, then it must list the entire contents all the time. Regards Russell -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
On 13/05/10 19:26, Russell Smith wrote: On 13/05/10 03:39, Tom Lane wrote: Russell Smith mr-r...@pws.com.au writes: pg_restore silently ignores the inclusion of -C when you do use a restore list. It would work as you expect if you use -C when creating the list file. The reason for this is that -C basically means don't skip the DATABASE entry. When you use -l without -C, you get a list file that doesn't include the DATABASE entry. In the last step, you need both -C and the DATABASE entry listed in the list file in order to have the CREATE DATABASE command emitted. We could possibly fix this by emitting the DATABASE entry during -l with or without -C. I'm not sure if that would have any bad side effects, but a quick test suggests that it will still skip the DATABASE entry if it's listed in the list file but -C isn't given. Of course, if you manually remove the DATABASE entry from the list file, you lose anyway. By the operation of other items (-C --data-only) passed with -l, it only produces to contents that would be restored with the other switches provided. If that's expect behavior, then the documentation of the switch is incorrect and should read something more like -l --list List the items in the archive that would be restored taking into account any other switches provided. The output of this operation can be used with the -L option to further restrict and reorder the items that are restored. Something like that as documentation for the switch would be clearer about what it's actually doing. The previous statement of listing the contents of the archive is clearly not what the switch is doing. If -l really should list the contents of the archive, then it must list the entire contents all the time. Further to these comments, both scenarios should make -l or -L incompatibe with -C --data-only -I -n --schema-only -T -t -x as all these alter the contents of what is restored. You should either use the list to control the items restored or the switches. Using both just created confusion. Regards Russell -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_restore ignores -C when using a restore list -L
Hi, pg_restore silently ignores the inclusion of -C when you do use a restore list. postgres$ pg_dump -Fc postgres postgres.dump postgres$ pg_restore -C postgres.dump | grep 'CREATE DATABASE' CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8'; ## Create a restore list postgres$ pg_restore -l postgres.dump list.txt ## Attempt to restore using the list postgres$ pg_restore -L list -C postgres.dump | grep 'CREATE DATABASE' The final result does not include an \connect or CREATE DATABASE statements. Expected behavior would be to have CREATE DATABASE in both cases. It would be at least desirable to remove the footgun and error if you are not allowed to use -C in this context. The example above does not involve the use of -d as a connection database. The results are particularly unexpected when you restore an entire database into the postgres by using pg_restore -L list -C -d postgres big_db.dump. Regards Russell -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
Russell Smith mr-r...@pws.com.au writes: pg_restore silently ignores the inclusion of -C when you do use a restore list. It would work as you expect if you use -C when creating the list file. The reason for this is that -C basically means don't skip the DATABASE entry. When you use -l without -C, you get a list file that doesn't include the DATABASE entry. In the last step, you need both -C and the DATABASE entry listed in the list file in order to have the CREATE DATABASE command emitted. We could possibly fix this by emitting the DATABASE entry during -l with or without -C. I'm not sure if that would have any bad side effects, but a quick test suggests that it will still skip the DATABASE entry if it's listed in the list file but -C isn't given. Of course, if you manually remove the DATABASE entry from the list file, you lose anyway. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_restore -t table : can silently omit constraints
(linux/Centos 5.2, postgres 84beta1, but also 8.3) Hello, I ran into a bug in pg_restore, where it silently omits to restore a constraint (here a primary key): (this is 8.4 beta 1, but the same occurs in 8.3) echo drop table if exists public.j; create table public.j (jnr integer NOT NULL primary key); | psql -d test ; psql -d test -c \d+ public.j pg_dump -F c -f ./public_j.dump -t j test psql -d test -c drop table if exists public.j; pg_restore -v -c -d test -t j ./public_j.dump The above will give the following output: DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index j_pkey for table j CREATE TABLE Table public.j Column | Type | Modifiers | Storage | Description +-+---+-+- jnr| integer | not null | plain | Indexes: j_pkey PRIMARY KEY, btree (jnr) Has OIDs: no DROP TABLE pg_restore: connecting to database for restore pg_restore: dropping TABLE j pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1490; 1259 17135 TABLE j rijkers pg_restore: [archiver (db)] could not execute query: ERROR: table j does not exist Command was: DROP TABLE public.j; pg_restore: creating TABLE j pg_restore: restoring data for table j pg_restore: setting owner and privileges for TABLE j WARNING: errors ignored on restore: 1 Table public.j Column | Type | Modifiers | Storage | Description +-+---+-+- jnr| integer | not null | plain | Has OIDs: no Result: table j gets restored, but without pk. Although it can be easily worked around, I think this should have behaved differently. It should have given an error about not restoring the primary key. ( Maybe it would even be preferrable if it refused to do this incomplete restore). thanks, Erik Rijkers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore -t table : can silently omit constraints
Erik Rijkers e...@xs4all.nl writes: I ran into a bug in pg_restore, where it silently omits to restore a constraint (here a primary key): pg_restore's notion of what -t means is very much more restricted than pg_dump's is. We might change this someday, but don't hold your breath (ie, it's a missing feature not a bug). In the example at hand I'd suggest just using the -t switch on the dump side and not passing anything to pg_restore. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore
On Aug 1, 2006, at 7:24 AM, Jan Cruz wrote: I am having problem with pg_restore 8.1.4 pg_restore: [custom archiver] out of memory pg_restore: *** aborted because of error Is that the only output you get? What's maintenance_work_mem set to, and how much memory and swap do you have? Is the machine swapping when this happens? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] pg_restore
I am having problem with pg_restore 8.1.4 pg_restore: [custom archiver] out of memorypg_restore: *** aborted because of error
[BUGS] pg_restore not work on windows 2000, when dump file size 4G
Hello, I run pg_restoreon windows. I meet a problem. It is ... pg_restore not work on windows 2000, when dump file size 4G and dump file format is custom. Error information: dir pg_100.dat 2005-12-13 09:27 4,391,630,151 pg_100.dat D:\Program Files\PostgreSQL\8.1\binpg_restore.exe -d postgres -Upostgres D:\PostgreSQL\backup\pg_100.datpg_restore: [archiver] file offset in dump file is too large thanks. Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) is intended only for the use of the intended recipient and may be confidential and/or privileged of Neusoft Group Ltd., its subsidiaries and/or its affiliates. If any reader of this communication is not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying is strictly prohibited, and may be unlawful. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete the original message and all copies from your system. Thank you.
Re: [BUGS] pg_restore not work on windows 2000, when dump file size 4G
On Tue, Aug 01, 2006 at 04:16:29PM +0800, Wang Haiyong wrote: Hello, I run pg_restore on windows. I meet a problem. It is ... pg_restore not work on windows 2000, when dump file size 4G and dump file format is custom. [...] pg_restore: [archiver] file offset in dump file is too large It's not by chance on a FAT partition? Regards -- tomas signature.asc Description: Digital signature
[BUGS] pg_restore parameter( -n) not work
Hello, I run pg_restoreon linux. I meet a problem. It is ... pg_restore 8.1.0parameter( -n) not work。 TEST CASE: [EMAIL PROTECTED] ~]$ createdb db5CREATE DATABASE[EMAIL PROTECTED] ~]$ psql db5Welcome to psql 8.1.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit db5=# create table t1(i int,j int);CREATE TABLEdb5=# create schema work;CREATE SCHEMAdb5=# create table work.t1(i int,j int);CREATE TABLEdb5-# \q[EMAIL PROTECTED] ~]$ pg_dump -d db5 -Fc a.out Oonly a little information is displayed.[EMAIL PROTECTED] ~]$ pg_restore -n work a.out PostgreSQL database dump-- SET client_encoding = 'EUC_CN';SET check_function_bodies = false;SET client_min_messages = warning; PostgreSQL database dump complete-- Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) is intended only for the use of the intended recipient and may be confidential and/or privileged of Neusoft Group Ltd., its subsidiaries and/or its affiliates. If any reader of this communication is not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying is strictly prohibited, and may be unlawful. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete the original message and all copies from your system. Thank you.
Re: [BUGS] pg_restore parameter( -n) not work
Wang Haiyong [EMAIL PROTECTED] writes: pg_restore 8.1.0 parameter( -n) not work¡£ This is fixed in 8.1.4. 2006-04-12 18:19 tgl * src/bin/pg_dump/: pg_backup_archiver.c, pg_restore.c (REL8_1_STABLE): Fix pg_restore -n option to do what the man page says it does. The original coding only worked if one of the selTypes restriction options was also given. Per report from Nick Johnson. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pg_restore problem
I had similar problems with the tar format. 3 smaller databases worked, the biggest did not. Switching to -Fc fixed it for me. Sorry for not reporting this. Harco Tom Lane wrote: Michael Andreasen [EMAIL PROTECTED] writes: pg_dump -Ft $db | bzip2 $db.dump.tbz ... pg_restore: [tar archiver] could not find header for file 3765.dat in tar archive Does it work better if you use -Fc format? There was a similar report recently, which makes me think the tar-format code has got some kind of problem, but without a test case it's hard to look into it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] pg_restore problem
I have a problem restoring our large database. This has only stared happening since we moved to 8.1.x We are currently on 8.1.3. We perform an over night dump of the database as follows; pg_dump -Ft $db | bzip2 $db.dump.tbz This happens fine, without any errors. On our backup machine also running 8.1.3 we do the following; createdb $db bzip2 -d -c $db.dump.tbz | pg_restore -i -O -d $db However, after a while we get this error; pg_restore: [tar archiver] could not find header for file 3765.dat in tar archive Our bzip2ed database is 1.2G so it's not small. We also run the same procedure on a number of other smaller database and have no problems at all. However, we are now in the unhandy position of having our main operational database that we can backup, but not restore! Help. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] pg_restore problem
Michael Andreasen [EMAIL PROTECTED] writes: pg_dump -Ft $db | bzip2 $db.dump.tbz ... pg_restore: [tar archiver] could not find header for file 3765.dat in tar archive Does it work better if you use -Fc format? There was a similar report recently, which makes me think the tar-format code has got some kind of problem, but without a test case it's hard to look into it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_restore error checking
Stephen Frost [EMAIL PROTECTED] writes: It seems like pg_restore really should be able to handle COPY errors correctly by skipping to the end of the COPY data segment when the initial COPY command comes back as an error. Send a patch ;-) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] pg_restore fails midstream
Using psql 8.1.0 on CentOS 4.2, I am trying to upgrade a 400GB database from pgsql 7.4.7. I made the dump using pg_dump from 8.1.0. After crunching for 5 hours, pg_restore reports: pg_restore: [custom archiver] could not uncompress data: incorrect data check the postgresql.log says: 2005-12-06 13:39:15 CET ERROR: unexpected message type 0x58 during COPY from stdin 2005-12-06 13:39:15 CET CONTEXT: COPY repl_activity, line 1923965 2005-12-06 13:39:15 CET LOG: could not send data to client: Broken pipe 2005-12-06 13:39:15 CET LOG: unexpected EOF on client connection This error was reported before here: http://archives.postgresql.org/pgsql-general/2005-08/msg01352.php Since I have about 100 schema's that have a repl_activity table, I had to resort to trial-and-error to figure out which one was causing problems. Creating a fresh dump of this table, and restoring it separately (pg_dump -s foo -t repl_activity ; pg_restore -L table_data_only.list) enabled me to continue with the original restore (from the point after the failed item). This bug is 2 fold in my opinion: 1) pg_restore should indicate which item causes a problem. Item number suffices, but name might be nice as well. 2) pg_dump appearantly has a bug with dumping some data, re-running pg_restore on the failed item reproduces the problem every time. I still have the dump (still restoring now), so I can run further diagnostics on it if needed. Sincerely, -- Richard van den Berg, CISSP --- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a| www.trust-factory.com 2518AK The Hague | Phone: +31 70 3620684 The Netherlands| Fax : +31 70 3603009 --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] pg_restore: internal data corruption?
From manual examination of the code, it looks like pg_restore will corrupt an internal data structure on certain abnormal inputs. File: postgresql-8.0.2/src/bin/pg_dummp/pg_backup_archiver.c SortTocFromFile pulls dump IDs out of a file and uses them to sort the list entries in field toc of archive handle AH. If the same ID appears twice in a row in the file (which seems reasonable, since the file is human-readable and a fair amount of input validation is already performed on it), it looks like this list will be corrupted: tePrev is initialized to the head of the list (L886). With each iteration of the inner loop, the entire list (including the sorted portion) is scanned for the entry with the ID just pulled off the file (L917). If the specified entry is found, it is moved to the position immediately after tePrev (L924), and tePrev is advanced (L925). Since tePrev is in the scanned portion of the list, it should be returned by getTocEntryByDumpId if the same ID is provided twice in a row. _moveAfter will then be called with aliased pointers, corrupting the list: the referenced value will have two self cycles, and will be skipped over by the next pointers but not the prev. Thanks for looking at this, Brian Hackett ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_restore: internal data corruption?
Brian Hackett [EMAIL PROTECTED] writes: From manual examination of the code, it looks like pg_restore will corrupt an internal data structure on certain abnormal inputs. Yeah, I think you are right. Looks easy to fix though: we already are building a flag array from the input, so just add a test to ignore any already-seen ID. Thanks for the report. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] pg_restore - Windows 2000
Hello all!! I'm in a trouble restoring a database. System: PostgreSQL 8.0 Windows 2000 Professional SP4 when I do pg_dump to backup mydb: C:\Archivos de programa\PostgreSQL\8.0\binpg_dump -U juan --format=c mydb back2 Password: pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte ll used the file back2 is generated and looks like good, but when I try to restore: C:\Archivos de programa\PostgreSQL\8.0\binpg_restore -U juan --format=c -d nueva back2 pg_restore: [archiver] unsupported version (1.13) in file header Is this a bug?, from already thanks Juan Espino ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] pg_restore -d base cumstom format broken in CVS head?
Fabien COELHO [EMAIL PROTECTED] writes: It seems to me that pg_restore -d base is broken in CVS head on my linux debian box. Yup. A patch intended to suppress comments actually suppressed *all* DDL commands from pg_restore :-(. Very sloppy work from Philip and/or Bruce ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] pg_restore many errors
Hi, I created a new database in the win32 version of 8.0 beta1 and added some functions. I then did a pg_dump like so: C:\Program Files\PostgreSQL\8.0-beta1\binpg_dump --username=postgres --format=c --file=c:\testdb.bak testdb the backup is created successfully. I then try to restore with pg_restore like this: and I get tons of errors (see below) I then go to the database in PGAdmin III and the only thing that is there is the tables and types, no functions where restored. I then did another dump, this time with --inserts and no compression and pasted it into a Pgadmin III query window and it all worked fine. as plain text all objects where created. pg_restore: [archiver (db)] could not execute query: ERROR: schema admin does not exist ng, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _insert(character varying, character varying, character varying, character varyi ng, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logoff(integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logoff(integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logoff(integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logoff(integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logoff(integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logon(character varying, character varying, integer, character varying, charact er varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logon(character varying, character varying, integer, character varying, charact er varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logon(character varying, character varying, integer, character varying, charact er varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logon(character varying, character varying, integer, character varying, charact er varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _logon(character varying, character varying, integer, character varying, charact er varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _password_change(integer, character varying, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _password_change(integer, character varying, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _password_change(integer, character varying, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _password_change(integer, character varying, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _password_change(integer, character varying, character varying) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update(character varying, character varying, character varying, character varyi ng, character varying, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update(character varying, character varying, character varying, character varyi ng, character varying, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update(character varying, character varying, character varying, character varyi ng, character varying, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update(character varying, character varying, character varying, character varyi ng, character varying, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update(character varying, character varying, character varying, character varyi ng, character varying, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update_gui_info(integer, integer, integer, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update_gui_info(integer, integer, integer, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update_gui_info(integer, integer, integer, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user _update_gui_info(integer, integer, integer, integer) does not exist pg_restore: [archiver (db)] could not execute query: ERROR: function spsys_user
[BUGS] pg_restore bug in ver 7.2.2?
Is there a bug in pg_restore 7.2.2 (didn't test in others) related to pg_restore? This take more than 14 hours to be done in a Database with 1.5 GB: $pg_restore -a -d DB2 -Fc -o -v DB1.dump why? TIA, Ricardo. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pg_restore --data-only ignored
Mike Finn [EMAIL PROTECTED] writes: While upgrading to 7.1.3 (from 7.1.2) I discovered that pg_restore was not honouring the --data-only command line option. Looks like this is already fixed in current sources. Thanks for the report though! regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS] pg_restore --data-only ignored
While upgrading to 7.1.3 (from 7.1.2) I discovered that pg_restore was not
honouring the --data-only command line option. Can't actually remember if
it worked in 7.1.2 or not.
Particulars:
postgresql-7.1.3.tar.gz
compiled under Linux mandrake 7.2
command used from a shell script
pg_restore --dbname=${dbName}2 --data-only -v --superuser=postgres $dbDump
I could get around this by using a list file and then editing it to only
include data lines, but thought it should be reported as a bug.
--
=
Mike Finn
Tactical Executive Systems
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
