Upgrading to v12

On 11/11/22 12:43, Brad White wrote:

I'm upgrading from v9.4 to v12.10 as a half step to 15.

Q1: How do I tell it which database to upgrade?
I only need the primary.
Not the half dozen restored copies.
Or do I need to detach everything I don't want copied?

1) If you are using pg_upgrade then it only works on the entire cluster
not individual databases.

2) This is not SQLite there is no detaching of databases.

3) I you want to move a single database then you are looking at
pg_dump/pg_restore or logical replication.

Q2: I get this error, and then at the end, it says "No error."

What was the complete pg_upgrade command you used?

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
SQL command failed
WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid,
0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace
n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm') AND
  ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_'
AND       n.nspname NOT IN ('pg_catalog', 'information_schema',
                'binary_upgrade', 'pg_toast') AND       c.oid >=
16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND
relname IN ('pg_largeobject') ))),   toast_heap (reloid, indtable,
toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM
regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid =
c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable,
toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM
pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid
IN         (SELECT reloid FROM regular_heap          UNION ALL
 SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname,
c.relname,   c.relfilenode, c.reltablespace,
pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT *
FROM regular_heap       UNION ALL       SELECT * FROM toast_heap
UNION ALL       SELECT * FROM all_index) all_rels   JOIN
pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN
pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER
JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;
ERROR:  could not access status of transaction 22316920
DETAIL:  Could not read from file "pg_clog/0015" at offset 73728: No error.

--
Adrian Klaver
adrian.klaver@aklaver.com

What was the complete pg_upgrade command you used?

"C:\Program Files\PostgreSQL\12\bin\pg_upgrade" -d "C:\Program
Files\PostgreSQL\9.4\data" -D "C:\Program Files\PostgreSQL\12\data" -b
"C:\Program Files\PostgreSQL\9.4\bin" -B "C:\Program
Files\PostgreSQL\12\bin" -U postgres -p 5432 -P 5435

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.

Brad White <b55white@gmail.com> writes:

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.

That's a pretty clear indication of data corruption. pg_upgrade
isn't going to be able to work with a corrupted source database,
so you'll have to do something to clear that before you can get
anywhere.

There's some advice about dealing with that here:

https://wiki.postgresql.org/wiki/Corruption

but in general the news is not going to be good.

regards, tom lane

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

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:

Can you do a pg_dump of that database?

Yes. No visible problems. No errors reported.

On Fri, Nov 11, 2022 at 3:17 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Brad White <b55white@gmail.com> writes:

Can you do a pg_dump of that database?

Yes. No visible problems. No errors reported.

Well, that's quite interesting, because pg_dump ought to read
all the same catalogs that pg_upgrade is failing to read.
So I'm not quite sure what's going on there. Nonetheless,
your path forward is clear: use pg_dump (or better pg_dumpall)
and then load the output into a freshly initdb'd v12 installation.
It'll be a bit slower than the pg_upgrade way, but it'll work.

regards, tom lane

On 11/11/22 14:06, Brad White wrote:

Can you do a pg_dump of that database?

Yes. No visible problems. No errors reported.

From your original post, what did "Not the half dozen restored copies"
mean?

In other words define the restore process.

--
Adrian Klaver
adrian.klaver@aklaver.com

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:

Sorry. Ignore the errors. That was mistakenly copied in from elsewhere.

On 11/11/22 18:41, Brad White wrote:

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"

Alright I am confused. You said you had multiple copies of the database
on one cluster. The above though shows you restoring to different
cluster(5433) then the cluster(5432) you dumped from.

Also why

"C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe

vs

$pgdir\pg_restore.exe
?

Is pgdir different from "C:\Program Files\PostgreSQL\9.4\bin\ ?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Nov 11, 2022, 9:57 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/11/22 18:41, Brad White wrote:

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"

Alright I am confused. You said you had multiple copies of the database
on one cluster.

yes. They've been deleted now, but we did.

The above though shows you restoring to different

cluster(5433) then the cluster(5432) you dumped from.

Yes. The backup is from production.
V9.4 is running on 5432 on all servers.
That particular restore happens to be on the dev server. 5433 is v12.

Also why

"C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe

vs

$pgdir\pg_restore.exe
?

Is pgdir different from "C:\Program Files\PostgreSQL\9.4\bin\ ?

"C:\Program Files... is from the backup script. Production is always only
on one version.

$pgdir is from the restore script.
So it might be
PostgreSQL\9.4\bin
or
PostgreSQL\12\bin
or
PostgreSQL\15\bin

Turns out that it doesn’t work well to mix the exe from one and the port
from another.

On 11/11/22 20:59, Brad White wrote:

On Fri, Nov 11, 2022, 9:57 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

Yes. The backup is from production.
V9.4 is running on 5432 on all servers.
That particular restore happens to be on the dev server. 5433 is v12.

1) This does not address from your OP:

"I only need the primary.
Not the half dozen restored copies."

And then from follow up post:

"I deleted all the other DBs and left only the primary.
Still getting the same error message, ending with"

How where the restored copies made on the original cluster?

2) For your explanation above, pg_dump from 9.4(5432) to pg_restore
12(5433) the issue would be ...\9.4\bin\pg_dump.exe of 9.4 and
pg_restore of said dump file to version 12. When moving up in version
you need to use the newer version of pg_dump(...\12\bin\pg_dump.exe) to
dump the 9.4 instance and then the version 12 pg_restore to the 12
instance. Both programs are backwards compatible, not forwards compatible.

--
Adrian Klaver
adrian.klaver@aklaver.com

Step #1: upgrade to 9.4.26.  You'll get *five years* of bug fixes.

(If the client lets you, of course.  I had servers stuck on 8.4.17 and 9.2.7
that were only upgraded because PCI auditors were going to tell my client's
client, and that scared /my/ client.  Now they're on 9.6.24...)

On 11/11/22 15:42, Brad White 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

--
Angular momentum makes the world go 'round.

When moving up in version you need to use the newer version of

pg_dump(...\12\bin\pg_dump.exe) to dump the 9.4 instance and then the
version 12 pg_restore to the 12 instance.
Oh my. That's a substantial change that could make a difference.

Thanks for catching that.

Step #1: upgrade to 9.4.26. You'll get *five years* of bug fixes.

Good idea.
I'll try 12 first, and if that doesn't work we'll go with this.

If the client lets you, of course.

Right? 8: -)

That's not a concern here.
A) They trust me, and
B) They only see the front end. They don't really care what happens with
the back end.

so long as
A) It doesn't break, and
B) We get replication working.