[GENERAL] Re: [GENERAL] PostgreSQL mirroring from RPM install to Source install

$ pg_controldata --version

pg_controldata (PostgreSQL) 9.4.1

$ echo $PGDATA

/apps/database/postgresql-data

$ pg_controldata $PGDATA

WARNING: Calculated CRC checksum does not match value stored in file.

Either the file is corrupt, or it has a different layout than this program

is expecting.  The results below are untrustworthy.

Segmentation fault (core dumped)

The master:

$ echo $PGDATA

/var/lib/pgsql/9.4/data

$ pg_controldata --version

pg_controldata (PostgreSQL) 9.4.1

$ pg_controldata $PGDATA

pg_control version number:            942

Catalog version number:               201409291

Database system identifier:           6158049305757004233

Database cluster state:               in production

pg_control last modified:             Thu 15 Dec 2016 11:19:04 AM PST

Latest checkpoint location:           44/E8FDC2F0

Prior checkpoint location:            44/E8EEE07C

Latest checkpoint's REDO location:    44/E8F72A4C

Latest checkpoint's REDO WAL file:    0000000100000044000000E8

Latest checkpoint's TimeLineID:       1

Latest checkpoint's PrevTimeLineID:   1

Latest checkpoint's full_page_writes: on

Latest checkpoint's NextXID:          0/231975962

Latest checkpoint's NextOID:          602794

Latest checkpoint's NextMultiXactId:  86

Latest checkpoint's NextMultiOffset:  171

Latest checkpoint's oldestXID:        40676549

Latest checkpoint's oldestXID's DB:   36696

Latest checkpoint's oldestActiveXID:  231975962

Latest checkpoint's oldestMultiXid:   1

Latest checkpoint's oldestMulti's DB: 421289

Time of latest checkpoint:            Thu 15 Dec 2016 11:18:39 AM PST

Fake LSN counter for unlogged rels:   0/1

Minimum recovery ending location:     0/0

Min recovery ending loc's timeline:   0

Backup start location:                0/0

Backup end location:                  0/0

End-of-backup record required:        no

Current wal_level setting:            hot_standby

Current wal_log_hints setting:        off

Current max_connections setting:      200

Current max_worker_processes setting: 8

Current max_prepared_xacts setting:   0

Current max_locks_per_xact setting:   64

Maximum data alignment:               4

Database block size:                  8192

Blocks per segment of large relation: 131072

WAL block size:                       8192

Bytes per WAL segment:                16777216

Maximum length of identifiers:        64

Maximum columns in an index:          32

Maximum size of a TOAST chunk:        2000

Size of a large-object chunk:         2048

Date/time type storage:               64-bit integers

Float4 argument passing:              by value

Float8 argument passing:              by reference

Data page checksum version:           0

Richard Brosnahan <broz@mac.com> writes:

I've got a PostgreSQL database server version 9.4.1 installed on an OEL 6 server. I have a second PostgreSQL database server version 9.4.1 running on an identical OEL 6 server. The first PostgreSQL was installed by system admins using RPM. The second PostgreSQL was installed from source, using --prefix to set the user and path to the installation. I want to run a mirror on the second server.

I do not have root, and cannot get root on these systems. Also, the sys admins now refuse to install PostgreSQL anywhere. This is really annoying!

I've followed all the instructions found here:

https://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION

The slave, running PostgreSQL 9.4.1 built from source, runs fine after I've run initdb and set things up.

When I replace its data directory as part of the mirroring instructions, using pg_basebackup, PostgreSQL won't start.

I get a checksum error, from pg_ctl.

2016-12-15 08:27:14.520 PST >FATAL: incorrect checksum in control file

My guess is that despite the versions being the same, the binaries are different.

You could try using pg_controldata to compare the pg_control contents;
it should be willing to print field values even if it thinks the checksum
is bad. It would be interesting to see (a) what the master's
pg_controldata prints about its pg_control, (b) what the slave's
pg_controldata prints about pg_control from a fresh initdb there, and
(c) what the slave's pg_controldata prints about the copied pg_control.

I am a little suspicious about whether the PG versions are really the same.
There's a bug in the 9.5.x series that it will issue a checksum complaint
not a version-number complaint if started against a 9.4.x pg_control.

                   regards, tom lane