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
[GENERAL] Backup & Restore a database in PostgreSQL
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. Thanks in advance, Siva. ::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 publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
