Backup and Restore (pg_dump & pg_restore)
Hello Team, We are getting below error while migrating pg_dump from Postgresql 9.6 to Postgresql 11.2 via pg_restore in docker environment. 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; Script used for pg_dump: - pg_dump -h 10.26.33.3 -p 5432 -U postgres -W -F c -v -f tmp/postgres/backup/backup10/ kbcn_backup19 kbcn >& tmp/postgres/backup/backup10/ kbcn_backup19.log; echo $? > tmp/postgres/backup/backup10/_'date+%Y-%m-%d.%H:%M:%S' Please advise. Regards, Daulat
Re: Backup and Restore (pg_dump & pg_restore)
On 4/21/19 9:35 AM, Daulat Ram wrote: Hello Team, We are getting below error while migrating pg_dump from Postgresql 9.6 to Postgresql 11.2 via pg_restore in docker environment. 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; Expected as the public schema is there by default. It is an informational error, you can ignore it. If you want to not see it and want a clean install on the 11.2 side use: -c --clean Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.) This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. on pg_restore side(along with --if-exists to remove other harmless error messages). FYI the -W on the pg_dump is redundant as the password will be prompted for without it: -W --password Force pg_dump to prompt for a password before connecting to a database. This option is never essential, since pg_dump will automatically prompt for a password if the server demands password authentication. However, pg_dump will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt. Script used for pg_dump: - pg_dump -h 10.26.33.3 -p 5432 -U postgres -W -F c -v -f tmp/postgres/backup/backup10/ kbcn_backup19 kbcn >& tmp/postgres/backup/backup10/ kbcn_backup19.log; echo $? > tmp/postgres/backup/backup10/_'date+%Y-%m-%d.%H:%M:%S' Please advise. Regards, Daulat -- Adrian Klaver adrian.kla...@aklaver.com
Re: Backup and Restore (pg_dump & pg_restore)
Adrian Klaver writes: > On 4/21/19 9:35 AM, Daulat Ram wrote: >> pg_restore: [archiver (db)] could not execute query: ERROR: schema >> "public" already exists >> Command was: CREATE SCHEMA public; > Expected as the public schema is there by default. It is an > informational error, you can ignore it. It's expected only if you made a dump file with 9.6's pg_dump and restored it with a later pg_restore; there were some changes in how the public schema got handled between the two versions. The usual recommendation when you are doing a version migration is to use the newer release's pg_dump to suck the data out of the older server. If you can't do that, it'll (probably) still work, but you may have cosmetic issues like this one. regards, tom lane
Display View Columns and Their Source Tables and Columns
I'm so close but I can't quite figure out how to match view columns to
their source columns in a query. Looks like I might need yet another
table to join that makes that match, but I'm not having any success
finding such a bridge. Matching views to their source tables works well
enough. What am I missing? Is there a better approach?
I would welcome any comments or leads that you have.
Thanks for your help!
Sue
Here is what I have so far:
select vcu.view_name view_name
,c.column_name view_column
,vcu.table_schema source_schema
,vcu.table_namesource_table
,vcu.column_name source_column
,c.is_updatableis_updatable
from information_schema.view_column_usage vcu
,information_schema.columns c
where vcu.view_schema = 'devops'
and vcu.table_schema in ('devops','chief','store')
and vcu.view_schema = c.table_schema
and vcu.view_name = c.table_name
and Help! *
order by vcu.view_name
,vcu.table_name
,c.column_name
;
--
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261
Re: Backup and Restore (pg_dump & pg_restore)
On 4/21/19 1:46 PM, Adrian Klaver wrote: On 4/21/19 9:35 AM, Daulat Ram wrote: Hello Team, We are getting below error while migrating pg_dump from Postgresql 9.6 to Postgresql 11.2 via pg_restore in docker environment. 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; Expected as the public schema is there by default. It is an informational error, you can ignore it. "Informational error" is a contradiction in terms. -- Angular momentum makes the world go 'round.
Re: Backup and Restore (pg_dump & pg_restore)
On 4/21/19 1:42 PM, Ron wrote: On 4/21/19 1:46 PM, Adrian Klaver wrote: On 4/21/19 9:35 AM, Daulat Ram wrote: Hello Team, We are getting below error while migrating pg_dump from Postgresql 9.6 to Postgresql 11.2 via pg_restore in docker environment. 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; Expected as the public schema is there by default. It is an informational error, you can ignore it. "Informational error" is a contradiction in terms. 1) Well the public schema was in the dump, so the OP wanted it. 2) It also existed in the target database. 3) The error let you know 1) & 2) 4) To my way of thinking it was a 'no harm, no foul' situation where the error just informed you that the target database took a side track to get where you wanted to be anyway. I see this sort of thing in monitoring systems e.g. environmental controls all the time. Things get flagged because they wander over set points intermittently. It is technically an error but unless they stay over the line it is just another data point. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Backup and Restore (pg_dump & pg_restore)
On 4/21/19 3:58 PM, Adrian Klaver wrote: On 4/21/19 1:42 PM, Ron wrote: On 4/21/19 1:46 PM, Adrian Klaver wrote: On 4/21/19 9:35 AM, Daulat Ram wrote: Hello Team, We are getting below error while migrating pg_dump from Postgresql 9.6 to Postgresql 11.2 via pg_restore in docker environment. 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; Expected as the public schema is there by default. It is an informational error, you can ignore it. "Informational error" is a contradiction in terms. 1) Well the public schema was in the dump, so the OP wanted it. 2) It also existed in the target database. 3) The error let you know 1) & 2) 4) To my way of thinking it was a 'no harm, no foul' situation where the error just informed you that the target database took a side track to get where you wanted to be anyway. I see this sort of thing in monitoring systems e.g. environmental controls all the time. Things get flagged because they wander over set points intermittently. It is technically an error but unless they stay over the line it is just another data point. Errors need to be fixed. If the restore can proceed without harm, then it's an Informational message. -- Angular momentum makes the world go 'round.
Re: Display View Columns and Their Source Tables and Columns
Hi, I don't know if it is possible ... the only way I found seems to use pg_depend and pg_rewrite as described here https://pgdba.org/post/2018/04/dependency_ladder/ Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Backup and Restore (pg_dump & pg_restore)
On 4/21/19 2:20 PM, Ron wrote: I see this sort of thing in monitoring systems e.g. environmental controls all the time. Things get flagged because they wander over set points intermittently. It is technically an error but unless they stay over the line it is just another data point. Errors need to be fixed. If the restore can proceed without harm, then it's an Informational message. That is a choice thing: https://www.postgresql.org/docs/11/app-pgrestore.html " -e --exit-on-error Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration. " It is also one of those eye of the beholder things as evidenced by: https://www.postgresql.org/docs/11/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT SeverityUsage syslog eventlog ... ERROR Reports an error ...WARNING ERROR ... Edited to keep on one line. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Display View Columns and Their Source Tables and Columns
Susan Hurst wrote: > I'm so close but I can't quite figure out how to match view columns to > their source columns in a query. There is no simple way to find which view column matches which column in the original table. Don't forget that the column could be defined as an expression that involves several columns of the base table. You'd have to parse pg_rewrite.ev_action. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
cache lookup failed for function 1 on fmgr_info_cxt_security, fmgr.c:184
Hello All, CentOS Linux release 7.5.1804 (Core) Postgres 10.7. When "create extension "uuid-ossp"", I got : 2019-04-22 02:41:53.323 UTC [10305] XX000ERROR: XX000: cache lookup failed for function 1 2019-04-22 02:41:53.323 UTC [10305] XX000LOCATION: fmgr_info_cxt_security, fmgr.c:184 2019-04-22 02:41:53.323 UTC [10305] XX000STATEMENT: create extension "uuid-ossp" ; I tried reinstall contrib module and even all postgres component, but still no luck. Try restarting the server also but no luck. Any idea how to resolve this ? Thanks, Soni
