[pgpool-general: 3212] Re: Problem creating database with replication mode (pgpool 3.1.12)
kirstein at tuhh.de
Wed Oct 8 03:15:24 JST 2014
i think i'm having the same problem.
I haven't found a valid solution for this yet, but seemingly stable
Here is what i found so far:
After Postgresql starts, you can find out with "pg_controldata [option]
[datadir]" what the "Latest checkpoint's NextOID" should be. If you now
create a new Database and ask for it's OID it should be the same.
After some (i guess) traffic, Postgresql pre-allocates an additional
full WAL block. You can see that there are significant more files in the
xlog folder than at start postgresql. If you now check "Latest
checkpoint's NextOID" you will find, that it has now advanced by roughly
a full block.
If you were to make a backup of this now (read "online-recovery") that
backup will start with this NextOID value while the original still runs
with the first OID.
I guess this behavior is to protect a recovered database against OID
I found, that if you shut down Postgresql, it will clear out it's
internal memory and save everything to disk. If you check "Latest
checkpoint's NextOID" now, you will find that it has reverted back to
the first value. My workaround consists of simply blocking all
connections to pgpool, restart all nodes and then recover the target node.
I still have to find a function that has the same effect as a full stop.
I had hoped one of those create checkpoint functions does so, but to no
avail for now.
If you happen to find a way to fix this please share it...
Am 07.10.2014 um 19:27 schrieb Jose Baez:
> I've got 2 virtual machine (Xen Server 6.2) with Debian Wheezy and:
> * PostgreSQL 9.1
> * Pgpool 3.1.12
> * Pacemaker/Corosync with pgpool-HA 2.2
> I tweaked the recovery scripts to do "online recovery". It went smooth
> both ways.
> However, when I "CREATE DATABASE" and connect through PgAdmin, Pgpool says:
> ERROR: pid 7628: read_kind_from_backend: *1 th kind C does not match
> with master or majority connection kind D*
> ERROR: pid 7628: kind mismatch among backends. Possible last query was:
> "SET DateStyle=ISO; SET client_min_messages=notice; SET
> bytea_output=escape; SELECT oid, pg_encoding_to_char(encoding) AS
> encoding, datlastsysoid
> FROM pg_database WHERE *oid = 18540" kind details are: 0[D] 1[C]*
> I checked database OID on each node and they are different (oid 18549
> and the oher is 26670). Is it normal?
> I created a few tables and they have indeed different OID in both
> If it is normal to have different OID in both nodes, why pgpool would
> If it should not happen, why would both nodes create an object with
> different OID? I checked in both nodes that all Pgpool extensions are
> installed (regclass, insert_lock, recovery, remote_start, switch_xlog)
> What field/file defines the next OID to use?
> At first, I thought the problem was the multiple statement limitation
> (sent by PgAdmin) but it is not, because when I do "online recovery"
> both nodes get same OID in every object and I can connect with PgAdmin
> (I found few links about same problem and nobody answered:
> I used PgAdmin from 1.16 to 1.20 with same behaviour.
> Thank you for your time!
> backend_hostname0 = 'psql1'
> backend_port0 = 5433
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/postgresql/9.1/main'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_hostname1 = 'psql2'
> backend_port1 = 5433
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/postgresql/9.1/main'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> connection_cache = on
> replication_mode = on
> replicate_select = off
> insert_lock = on
> lobj_lock_table = ''
> load_balance_mode = off
> master_slave_mode = off
> parallel_mode = off
> pgpool-general mailing list
> pgpool-general at pgpool.net
More information about the pgpool-general