Postgres Pro
Downloads
Home   >   mailing lists

Re: Upgrading to v12 - Mailing list pgsql-general

From Brad White
Subject Re: Upgrading to v12
Date
Msg-id CAA_1=91E8kcce-xuXTTPJaZnX-To8dOHPe3ZBKVoyZyvXNyaFw@mail.gmail.com
Whole thread Raw
In response to Re: Upgrading to v12  (Brad White <b55white@gmail.com>)
Responses Re: Upgrading to v12  (Brad White <b55white@gmail.com>)
Re: Upgrading to v12  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
> From your original post, what did "Not the half dozen restored copies"
mean?
Over time, we've restored multiple copies for testing and reproducing various issues. 

I'm only trying to set up replication one one of those copies. 

> In other words define the restore process.

Command to back up the database:
"C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --no-password  --format custom --blobs --verbose --file "DB_"%datestr%"\DB_"%datestr%.backup "DB"

Restore:
$pgdir\pg_restore.exe -h localhost -p 5433 -U postgres --no-password --clean --if-exists --format=custom --dbname="DB_test" "C:\Temp\DB_20220922_2300\DB_20220922_2300.backup"

The errors looked insignificant to me. The database comes up and appears to be perfectly functional.

pg_restore.exe : pg_restore: while INITIALIZING:
At C:\Temp\Restore12.ps1:36 char:2
+     &$prestore -h $phost -p $pport -U postgres --no-password --clean --if-exists -- ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (pg_restore: while INITIALIZING::String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
pg_restore: error:
could not execute query: ERROR:  unrecognized configuration parameter "idle_in_transaction_session_timeout"
Command was: SET idle_in_transaction_session_timeout = 0;
(Note: This is a backup from 9, being restored to 12. I assume that 

pg_restore: error:
 could not execute query: ERROR:  unrecognized configuration parameter "row_security"
Command was: SET row_security = off;

pg_restore
: WARNING:  column "Button2" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore: WARNING:  column "Button3" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore
: WARNING:  column "Button4" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore: WARNING:  column "Button5" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore
: WARNING:  column "Button6" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore
: WARNING:  column "Button7" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

pg_restore
: warning: errors ignored on restore: 2

On Fri, Nov 11, 2022 at 3:42 PM Brad White <b55white@gmail.com> wrote:
I'm practicing on our Dev server, so I can blow this away and reload at any time.
Are there any utilities to check for corruption on my Prod server in v9.4.1?

All my backups are done with pg_dump.exe, so that's where this database came from in the first place.
So we know that pg_dump.exe works on Prod at least.

On Fri, Nov 11, 2022 at 3:17 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/11/22 13:11, Brad White wrote:
> I deleted all the other DBs and left only the primary.
> Still getting the same error message, ending with
>
> ERROR:  could not access status of transaction 22316920
> DETAIL:  Could not read from file "pg_clog/0015" at offset 73728: No error.

Can you do a pg_dump of that database?


--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Upgrading to v12
Next
From: Brad White
Date:
Subject: Re: Upgrading to v12