Re: [GENERAL] Backup/Restore bytea data
On 14/01/13 01:13, sub3 wrote: Hi, I am having an issue upgrading a really old 8.2 db up to 9.2.2. One of the tables contains a bytea field. When I backup& restore using pgadmin from my 9.2.2 install, it doesn't convert this field correctly. Could this be due to your bytea_output setting? http://www.postgresql.org/docs/9.2/static/datatype-binary.html Not sure how this could snag you if you are dumping using 9.2, but this: I see it starts w/special character when selecting it from the old database; in the new db, I see a string starting w/"\211PNG". is clearly in "escape" rather than "hex" format. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup & Restore a database in PostgreSQL
Siva Palanisamy wrote: > However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I > could not able to comprehend the error list! I don't have any clue about it! > > For your information, I don't think am running using the superuser account! But just seem like a > normal user account.. Could this lead to few issues on privileges? The messages you get are to be expected if you try to restore a database without the necessary permissions. It worked for the objects that the user owns. If you want to backup just a few tables or a certain schema in the database, use the -t or -n flags. If you want to backup the whole database, make sure you have appropriate permissions when you restore. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup & Restore a database in PostgreSQL
Siva -- > > Thanks a lot for your reply. As usual Backup worked perfectly. When I tried > restore using the command you provided, I got the below list of errors! > Please help me > out on this. > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION > plpgsql_call_handler() postgres > pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of > function public.plpgsql_call_handler > Command was: DROP FUNCTION public.plpgsql_call_handler(); Clue #1 -- you are not running as the correct user for the restore into this database. > pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL > LANGUAGE plpgsql > pg_restore: [archiver (db)] could not execute query: ERROR: must be > superuser to drop procedural language > Command was: DROP PROCEDURAL LANGUAGE plpgsql; Clue #2! So you need to change to being the postgres user that created the database, which presumably has the proper permissions to drop these various entities. http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup & Restore a database in PostgreSQL
Hi Amitabh, I just added the -O option in the middle as detailed below. pg_restore -c -O -h localhost -U username -d db dumpfile.pg; However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I could not able to comprehend the error list! I don't have any clue about it! For your information, I don't think am running using the superuser account! But just seem like a normal user account.. Could this lead to few issues on privileges? Any help would be greatly appreciated. Thanks and Regards, Siva. From: Amitabh Kant [mailto:amitabhk...@gmail.com] Sent: Monday, August 08, 2011 2:42 PM To: Siva Palanisamy Cc: John R Pierce; pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy mailto:siv...@hcl.com>> wrote: Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAG... pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges were granted pg_restore: WARNING: no privileges were granted WARNING: errors ignored on restore: 7 Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org> [mailto:pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org>] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org> Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL On 08/08/11 1:01 AM, Siva Palanisamy wrote: > > Hi All, > > I am also a newbie here! I need to backup a database and restore it > into the target machine where the database may already present or > might not. If it exists, I want the "restore" command to overwrite, > otherwise, just create a new one. > > I tried using the commands: > > (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out; > > RESTORE: pg_dump -h localhost -U username db < dump_file.out; > > (2) BACKUP: > > pg_dump -h localhost -U username -Ft db > dump_file.tar; > > RESTORE: > > pg_restore -h localhost -U username -d db dump_file.tar; > > "Backup" worked perfect in the above 2 scenarios whereas "restore" > didn't yield the exact results. For testing it, I took the back-up and > intentionally deleted few records in a table. I then restored the > database in the same machine where the database exists. I expected the > deleted records to come back as I was restoring the one which has the > complete data. It didn't yield proper results. And I wonder why.. > > I believe I might be doing something marginally wrong. I would > appreciate if any geek over here to guide me the "resto
Re: [GENERAL] Backup & Restore a database in PostgreSQL
Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy wrote: > Hi John, > > Thanks a lot for your reply. As usual Backup worked perfectly. When I tried > restore using the command you provided, I got the below list of errors! > Please help me out on this. > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION > plpgsql_call_handler() postgres > pg_restore: [archiver (db)] could not execute query: ERROR: must be owner > of function public.plpgsql_call_handler >Command was: DROP FUNCTION public.plpgsql_call_handler(); > pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL > LANGUAGE plpgsql > pg_restore: [archiver (db)] could not execute query: ERROR: must be > superuser to drop procedural language >Command was: DROP PROCEDURAL LANGUAGE plpgsql; > pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public > postgres > pg_restore: [archiver (db)] could not execute query: ERROR: must be owner > of schema public >Command was: DROP SCHEMA public; > pg_restore: [archiver (db)] could not execute query: ERROR: schema > "public" already exists >Command was: CREATE SCHEMA public; > pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA > public postgres > pg_restore: [archiver (db)] could not execute query: ERROR: must be owner > of schema public >Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; > pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL > LANGUAGE plpgsql > pg_restore: [archiver (db)] could not execute query: ERROR: must be > superuser to create procedural language >Command was: CREATE PROCEDURAL LANGUAGE plpgsql; > pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION > plpgsql_call_handler() postgres > pg_restore: [archiver (db)] could not execute query: ERROR: permission > denied for language c >Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS > language_handler >AS '$libdir/plpgsql', 'plpgsql_call_handler' >LANGUAG... > pg_restore: WARNING: no privileges could be revoked > pg_restore: WARNING: no privileges could be revoked > pg_restore: WARNING: no privileges were granted > pg_restore: WARNING: no privileges were granted > WARNING: errors ignored on restore: 7 > > Thanks and Regards, > Siva. > > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce > Sent: Monday, August 08, 2011 1:45 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL > > On 08/08/11 1:01 AM, Siva Palanisamy wrote: > > > > Hi All, > > > > I am also a newbie here! I need to backup a database and restore it > > into the target machine where the database may already present or > > might not. If it exists, I want the "restore" command to overwrite, > > otherwise, just create a new one. > > > > I tried using the commands: > > > > (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out; > > > > RESTORE: pg_dump -h localhost -U username db < dump_file.out; > > > > (2) BACKUP: > > > > pg_dump -h localhost -U username -Ft db > dump_file.tar; > > > > RESTORE: > > > > pg_restore -h localhost -U username -d db dump_file.tar; > > > > "Backup" worked perfect in the above 2 scenarios whereas "restore" > > didn't yield the exact results. For testing it, I took the back-up and > > intentionally deleted few records in a table. I then restored the > > database in the same machine where the database exists. I expected the > > deleted records to come back as I was restoring the one which has the > > complete data. It didn't yield proper results. And I wonder why.. > > > > I believe I might be doing something marginally wrong. I would > > appreciate if any geek over here to guide me the "restore" command > > properly. > > > > > > specify -c on the pg_restore, and it will drop the database objects and > recreate them > > pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname > > pg_restore -c -h localhost -U user -d dbname dumpfile.pg > > > > > -- > john r pierceN 37, W 122 > santa cruz ca mid-left coast > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresq
Re: [GENERAL] Backup & Restore a database in PostgreSQL
Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler Command was: DROP FUNCTION public.plpgsql_call_handler(); pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language Command was: DROP PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public Command was: COMMENT ON SCHEMA public IS 'Standard public schema'; pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAG... pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges could be revoked pg_restore: WARNING: no privileges were granted pg_restore: WARNING: no privileges were granted WARNING: errors ignored on restore: 7 Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL On 08/08/11 1:01 AM, Siva Palanisamy wrote: > > Hi All, > > I am also a newbie here! I need to backup a database and restore it > into the target machine where the database may already present or > might not. If it exists, I want the "restore" command to overwrite, > otherwise, just create a new one. > > I tried using the commands: > > (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out; > > RESTORE: pg_dump -h localhost -U username db < dump_file.out; > > (2) BACKUP: > > pg_dump -h localhost -U username -Ft db > dump_file.tar; > > RESTORE: > > pg_restore -h localhost -U username -d db dump_file.tar; > > "Backup" worked perfect in the above 2 scenarios whereas "restore" > didn't yield the exact results. For testing it, I took the back-up and > intentionally deleted few records in a table. I then restored the > database in the same machine where the database exists. I expected the > deleted records to come back as I was restoring the one which has the > complete data. It didn't yield proper results. And I wonder why.. > > I believe I might be doing something marginally wrong. I would > appreciate if any geek over here to guide me the "restore" command > properly. > > specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or pub
Re: [GENERAL] Backup & Restore a database in PostgreSQL
On 08/08/11 1:01 AM, Siva Palanisamy wrote: Hi All, I am also a newbie here! I need to backup a database and restore it into the target machine where the database may already present or might not. If it exists, I want the "restore" command to overwrite, otherwise, just create a new one. I tried using the commands: (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out; RESTORE: pg_dump -h localhost -U username db < dump_file.out; (2) BACKUP: pg_dump -h localhost -U username -Ft db > dump_file.tar; RESTORE: pg_restore -h localhost -U username -d db dump_file.tar; "Backup" worked perfect in the above 2 scenarios whereas "restore" didn't yield the exact results. For testing it, I took the back-up and intentionally deleted few records in a table. I then restored the database in the same machine where the database exists. I expected the deleted records to come back as I was restoring the one which has the complete data. It didn't yield proper results. And I wonder why.. I believe I might be doing something marginally wrong. I would appreciate if any geek over here to guide me the "restore" command properly. specify -c on the pg_restore, and it will drop the database objects and recreate them pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore Needed for Upgrade from 9.0beta4?
On Tuesday, February 08, 2011 10:41:15 am Lee Hughes wrote: > From section 15.4 of the manual: > > "If you are upgrading from PostgreSQL "9.0.x", the new version can use > your current data files so you should skip the backup and restore steps" > > Is 9.0beta4 considered a 9.0.x version, or do I need to backup/restore > when upgrading from that version? > > Thanks much- > > Lee This announcement for the Postgres 9.0rc1 indicated a dump and restore for migration from the betas. I take this to mean yes to dump/restore for what you are doing. http://www.postgresql.org/about/news.1230 -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Fri, 2008-07-18 at 20:25 -0400, Francisco Reyes wrote: > Does pg_snapclone works mostly on large rows or will it also be faster > than pg_dump for narrow tables? It allows you to run your dump in multiple pieces. Thats got nothing to do with narrow or wide. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
Simon Riggs wrote: Have a look at pg_snapclone. It's specifically designed to significantly improve dump times for very large objects. http://pgfoundry.org/projects/snapclone/ Also, in case the original poster is not aware, by default pg_dump allows to backup single tables. Just add -t . Does pg_snapclone works mostly on large rows or will it also be faster than pg_dump for narrow tables? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, 2008-05-07 at 15:24 -0700, John Smith wrote:
> Actually, I forgot to mention one more detail in my original post.
> For the table that we're looking to backup, we also want to be able to
> do incremental backups. pg_dump will cause the entire table to be
> dumped out each time it is invoked.
>
> With the pg_{start,stop}_backup approach, incremental backups could be
> implemented by just rsync'ing the data files for example and applying
> the incremental WALs. So if table foo didn't change very much since
> the first backup, we would only need to rsync a small amount of data
> plus the WALs to get an incremental backup for table foo.
>
> Besides picking up data on unwanted tables from the WAL (e.g., bar
> would appear in our recovered database even though we only wanted
> foo), do you see any other problems with this pg_{start,stop}_backup
> approach? Admittedly, it does seem a bit hacky.
You wouldn't be the first to ask to restore only a single table.
I can produce a custom version that does that if you like, though I'm
not sure that feature would be accepted into the main code.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
Hi Tom,
Actually, I forgot to mention one more detail in my original post.
For the table that we're looking to backup, we also want to be able to
do incremental backups. pg_dump will cause the entire table to be
dumped out each time it is invoked.
With the pg_{start,stop}_backup approach, incremental backups could be
implemented by just rsync'ing the data files for example and applying
the incremental WALs. So if table foo didn't change very much since
the first backup, we would only need to rsync a small amount of data
plus the WALs to get an incremental backup for table foo.
Besides picking up data on unwanted tables from the WAL (e.g., bar
would appear in our recovered database even though we only wanted
foo), do you see any other problems with this pg_{start,stop}_backup
approach? Admittedly, it does seem a bit hacky.
Thanks,
- John
On Wed, May 7, 2008 at 2:41 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "John Smith" <[EMAIL PROTECTED]> writes:
> > After reading the documentation, it seems like the following might
> > work. Suppose the database has two tables foo and bar, and we're only
> > interested in backing up table foo:
>
> > 1. Call pg_start_backup
>
> > 2. Use the pg_class table in the catalog to get the data file names
> > for tables foo and bar.
>
> > 3. Copy the system files and the data file for foo. Skip the data file
> for bar.
>
> > 4. Call pg_stop_backup()
>
> > 5. Copy WAL files generated between 1. and 4. to another location.
>
> > Later, if we want to restore the database somewhere with just table
> > foo, we just use postgres's normal recovery mechanism and point it at
> > the files we backed up in 2. and the WAL files from 5.
>
> > Does anyone see a problem with this approach
>
> Yes: it will not work, not even a little bit, because the WAL files will
> contain updates for all the tables. You can't just not have the tables
> there during restore.
>
> Why are you not using pg_dump?
>
> regards, tom lane
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
"John Smith" <[EMAIL PROTECTED]> writes: > After reading the documentation, it seems like the following might > work. Suppose the database has two tables foo and bar, and we're only > interested in backing up table foo: > 1. Call pg_start_backup > 2. Use the pg_class table in the catalog to get the data file names > for tables foo and bar. > 3. Copy the system files and the data file for foo. Skip the data file for > bar. > 4. Call pg_stop_backup() > 5. Copy WAL files generated between 1. and 4. to another location. > Later, if we want to restore the database somewhere with just table > foo, we just use postgres's normal recovery mechanism and point it at > the files we backed up in 2. and the WAL files from 5. > Does anyone see a problem with this approach Yes: it will not work, not even a little bit, because the WAL files will contain updates for all the tables. You can't just not have the tables there during restore. Why are you not using pg_dump? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, 2008-05-07 at 13:02 -0700, John Smith wrote: > I have a large database (multiple TBs) where I'd like to be able to do > a backup/restore of just a particular table (call it foo). Because > the database is large, the time for a full backup would be > prohibitive. Also, whatever backup mechanism we do use needs to keep > the system online (i.e., users must still be allowed to update table > foo while we're taking the backup). Have a look at pg_snapclone. It's specifically designed to significantly improve dump times for very large objects. http://pgfoundry.org/projects/snapclone/ -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, 7 May 2008 16:09:45 -0400 "David Wilson" <[EMAIL PROTECTED]> wrote: > I'm fairly certain that you have to be very careful about doing simple > file copies while the system is running, as the files may end up out > of sync based on when each individual one is copied. I haven't done it > myself, but I do know that there are a lot of caveats that someone > with more experience doing that type of backup can hopefully point you > to. Besides the fact that it seems to be a fairly hacky thing to do... it is going to be fragile. Consider: (serverA) create table foo(); (serverB) create table foo(); (serverA) Insert stuff; (serverA) Alter table foo add column; Oops... (serverA) alter table foo drop column; You now have different version of the files than on serverb regardless of the table name. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate signature.asc Description: PGP signature
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, 7 May 2008 13:02:57 -0700 "John Smith" <[EMAIL PROTECTED]> wrote: > Hi, > > I have a large database (multiple TBs) where I'd like to be able to do > a backup/restore of just a particular table (call it foo). Because > the database is large, the time for a full backup would be > prohibitive. Also, whatever backup mechanism we do use needs to keep > the system online (i.e., users must still be allowed to update table > foo while we're taking the backup). > Does anyone see a problem with this approach (e.g., correctness, > performance, etc.)? Or is there perhaps an alternative approach using > some other postgresql mechanism that I'm not aware of? Why are you not just using pg_dump -t ? Are you saying the backup of the single table pg_dump takes to long? Perhaps you could use slony with table sets? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate signature.asc Description: PGP signature
Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, May 7, 2008 at 4:02 PM, John Smith <[EMAIL PROTECTED]> wrote: > Does anyone see a problem with this approach (e.g., correctness, > performance, etc.)? Or is there perhaps an alternative approach using > some other postgresql mechanism that I'm not aware of? Did you already look at and reject pg_dump for some reason? You can restrict it to specific tables to dump, and it can work concurrently with a running system. Your database is large, but how large are the individual tables you're interested in backing up? pg_dump will be slower than a file copy, but may be sufficient for your purpose and will have guaranteed correctness. I'm fairly certain that you have to be very careful about doing simple file copies while the system is running, as the files may end up out of sync based on when each individual one is copied. I haven't done it myself, but I do know that there are a lot of caveats that someone with more experience doing that type of backup can hopefully point you to. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Restore
Bob Pawley wrote: Found it in template 1. This seems strange as both servers and pgadmins are the same version and I haven't opened the template until today. The only thing I can think of is that you accidentally restored into template1. Probably easier to do with pgadmin than from the command-line. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Backup Restore
Found it in template 1. This seems strange as both servers and pgadmins are the same version and I haven't opened the template until today. Bob - Original Message - From: "Richard Huxton" To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Dave Page" <[EMAIL PROTECTED]>; "Shoaib Mir" <[EMAIL PROTECTED]>; "Postgresql" Sent: Wednesday, January 03, 2007 10:44 AM Subject: Re: [GENERAL] Backup Restore Bob Pawley wrote: Looking through PGAdmin where would I find the spatial references that the errror message references? Bob Pawley wrote: Following is the error message on pg_restore:- "pg_restore: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." Looking at this, you should have a table "spatial_ref_sys" with a primary key constraint "spatial_ref_sys_pkey" Find the table via the left-hand tree conrol and its details should be listed on the right-hand side. The primary key will be detailed at the top and bottom -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
Re: [GENERAL] Backup Restore
Bob Pawley wrote: Looking through PGAdmin where would I find the spatial references that the errror message references? Bob Pawley wrote: Following is the error message on pg_restore:- "pg_restore: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." Looking at this, you should have a table "spatial_ref_sys" with a primary key constraint "spatial_ref_sys_pkey" Find the table via the left-hand tree conrol and its details should be listed on the right-hand side. The primary key will be detailed at the top and bottom -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backup Restore
Looking through PGAdmin where would I find the spatial references that the errror message references? Bob - Original Message - From: "Richard Huxton" To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Dave Page" <[EMAIL PROTECTED]>; "Shoaib Mir" <[EMAIL PROTECTED]>; "Postgresql" Sent: Tuesday, January 02, 2007 2:00 AM Subject: Re: [GENERAL] Backup Restore Bob Pawley wrote: Following is the error message on pg_restore:- "pg_restore: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: *** aborted because of error Process returned exit code 1." The GIS feature was removed from the PostgreSQL application before the project dump. Not sure what you mean by that - you removed all GIS related types and functions from the source database? Anyone have any thoughts on how to get around this?? Remove the constraint if you no longer have that requirement. It looks like you have a primary-key defined on spatial_ref_sys and want to remove it. See the SQL Reference section of the manuals for how to use ALTER TABLE to drop primary keys and other constraints. I'm curious as to how this can happen though. Are the definitions of table spatial_ref_sys the same in the source and target database? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Backup Restore
Hi Dave I can get the restore working if I dump the project spelling out "*.backup" and not relying on the default. However the restore is being aborted due to a pk error for the spatial coordinates. I've removed the gis feature from both applications but still get the error. Any thoughts?? Bob - Original Message - From: "Dave Page" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Shoaib Mir" <[EMAIL PROTECTED]>; "Postgresql" Sent: Friday, December 29, 2006 12:10 AM Subject: Re: [GENERAL] Backup Restore Bob Pawley wrote: When I change it to view "all files" it's there - but it won't do anything. So I assume you've used a different extension than the one the dialogue is expecting by default? When you say "it won't do anything." do you mean you cannot select the file, or that nothing happens when you select it and run the restore? If the former, does it work if you rename the file to use the expected extension? Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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
Re: [GENERAL] Backup Restore
Bob Pawley wrote: Following is the error message on pg_restore:- "pg_restore: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: *** aborted because of error Process returned exit code 1." The GIS feature was removed from the PostgreSQL application before the project dump. Not sure what you mean by that - you removed all GIS related types and functions from the source database? Anyone have any thoughts on how to get around this?? Remove the constraint if you no longer have that requirement. It looks like you have a primary-key defined on spatial_ref_sys and want to remove it. See the SQL Reference section of the manuals for how to use ALTER TABLE to drop primary keys and other constraints. I'm curious as to how this can happen though. Are the definitions of table spatial_ref_sys the same in the source and target database? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backup Restore
Following is the error message on pg_restore:- "pg_restore: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: duplicate key violates unique constraint "spatial_ref_sys_pkey" CONTEXT: COPY spatial_ref_sys, line 1: "2000 EPSG 2000 PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Angui..." pg_restore: *** aborted because of error Process returned exit code 1." The GIS feature was removed from the PostgreSQL application before the project dump. Anyone have any thoughts on how to get around this?? Bob - Original Message - From: "Dave Page" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Shoaib Mir" <[EMAIL PROTECTED]>; "Postgresql" Sent: Friday, December 29, 2006 10:57 AM Subject: Re: [GENERAL] Backup Restore Bob Pawley wrote: Hi Dave I can get the restore working if I dump the project spelling out "*.backup" and not relying on the default. However the restore is being aborted due to a pk error for the spatial coordinates. I've removed the gis feature from both applications but still get the error. Any thoughts?? Nope, someone else will need to help with that I'm afraid. The exact error message would make it infinitely easier to help you though. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Backup Restore
Bob Pawley wrote: Hi Dave I can get the restore working if I dump the project spelling out "*.backup" and not relying on the default. However the restore is being aborted due to a pk error for the spatial coordinates. I've removed the gis feature from both applications but still get the error. Any thoughts?? Nope, someone else will need to help with that I'm afraid. The exact error message would make it infinitely easier to help you though. Regards, Dave ---(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
Re: [GENERAL] Backup Restore
Bob Pawley wrote: When I change it to view "all files" it's there - but it won't do anything. So I assume you've used a different extension than the one the dialogue is expecting by default? When you say "it won't do anything." do you mean you cannot select the file, or that nothing happens when you select it and run the restore? If the former, does it work if you rename the file to use the expected extension? Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Backup Restore
When I change it to view "all files" it's there - but it won't do anything. Bob - Original Message - From: "Dave Page" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Shoaib Mir" <[EMAIL PROTECTED]>; "Postgresql" Sent: Thursday, December 28, 2006 12:06 PM Subject: Re: [GENERAL] Backup Restore Bob Pawley wrote: I'm not getting an error message. The restore utility just doesn't see the backup file. It's looking for a file *.backup which is there but noot seen. It's a standard file dialogue as provided by your operating system. I can't think of any reason it wouldn't see the file unless you are looking in the wrong place. I am running a version that identifies itself as 1.6.2. I downloaded it a few weeks ago from the official site. Should I be using an earlier version?? You must have downloaded an SVN snapshot version. They are available on the official site, but it's not easy to mistake them for actual release versions. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Backup Restore
Bob Pawley wrote: I'm not getting an error message. The restore utility just doesn't see the backup file. It's looking for a file *.backup which is there but noot seen. It's a standard file dialogue as provided by your operating system. I can't think of any reason it wouldn't see the file unless you are looking in the wrong place. I am running a version that identifies itself as 1.6.2. I downloaded it a few weeks ago from the official site. Should I be using an earlier version?? You must have downloaded an SVN snapshot version. They are available on the official site, but it's not easy to mistake them for actual release versions. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backup Restore
I'm not getting an error message. The restore utility just doesn't see the backup file. It's looking for a file *.backup which is there but noot seen. I am running a version that identifies itself as 1.6.2. I downloaded it a few weeks ago from the official site. Should I be using an earlier version?? Bob - Original Message - From: "Dave Page" <[EMAIL PROTECTED]> To: "Shoaib Mir" <[EMAIL PROTECTED]> Cc: "Bob Pawley" <[EMAIL PROTECTED]>; "Postgresql" Sent: Thursday, December 28, 2006 11:30 AM Subject: Re: [GENERAL] Backup Restore Shoaib Mir wrote: Well I haven't use much of PGAdmin but I will always be using pg_dump and pg_restore for that as they are really easy to use. You can find help on backup and restore at --> http://www.postgresql.org/docs/8.2/static/backup.html pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes. Perhaps Bob could share whatever error message he gets with us? Oh, and there is no released 1.6.2 version of pgAdmin - that would be head of the current stable branch. Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Backup Restore
Shoaib Mir wrote: Well I haven't use much of PGAdmin but I will always be using pg_dump and pg_restore for that as they are really easy to use. You can find help on backup and restore at --> http://www.postgresql.org/docs/8.2/static/backup.html pgAdmin is just a frontend to pg_dump/pg_restore for backup purposes. Perhaps Bob could share whatever error message he gets with us? Oh, and there is no released 1.6.2 version of pgAdmin - that would be head of the current stable branch. Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Backup Restore
Well I haven't use much of PGAdmin but I will always be using pg_dump and pg_restore for that as they are really easy to use. You can find help on backup and restore at --> http://www.postgresql.org/docs/8.2/static/backup.html Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/28/06, Bob Pawley <[EMAIL PROTECTED]> wrote: PostgreSQL 8.1 running on Win XP. PGAdmin Version 1.6.2 When I backup my database on one computer using PG Admin 3 then attempt to open the file on a second computer - PG Admin does NOT recognize the file. The documentation isn't helpful, at least for a neophyte such as I. Can someone point me to a simple instruction set for a backup/restore operation using the PG Admin resource?? Bob
Re: [GENERAL] Backup restore does not work
Art Fore wrote: Using PGadminIII, I backup my database on linux machine, and try to restore it on the windows machine. Alway comes up with duplicate key on the first item. I drop all views and the only table in the database and try again. Still same thing. I do a refrresh, none of the views or table were dropped. What is the secret of backup-restore is pgadmin? Well views are not what are going to cause the duplicate key. A duplicate key error is caused by a violation of a unique index or primary key. Do you have duplicate data? Or perhaps a partially restored table structure? Sincerely, Joshua D. Drake Art ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
