<div dir="ltr">Michel, you are using Pgpool 3.3 and I am using 3.1.12 (I even downgraded to 3.1.7 to check, but same error happens). From other posts I posted people used 2.1 and 2.2<div><br></div><div><div>We both are using PostgreSQl 9.1. so<b> I haveI upgraded to 9.3. Same error happens.</b> :((</div><div><br></div><div>I dont think it's Pgpool because creating database/tables is something basic for everyday use, and developers should had faced this OID's problem, shouldn't they?</div><div><br></div><div>Could be my postgresql.conf or recovery scripts??</div><div><br></div><div><br></div><div>I thinkg most important fields changed from my <b><u>postgresql.conf</u></b> are:<br></div></div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div><div><font face="courier new, monospace" color="#660000"><br></font></div></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div><div><font face="courier new, monospace" color="#660000">port=5433</font></div></div><div><font face="courier new, monospace" color="#660000">...</font></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div><div><font face="courier new, monospace" color="#660000">wal_level = archive</font></div></div><div><div><div><font face="courier new, monospace" color="#660000">archive_mode = on</font></div></div></div><div><div><div><font face="courier new, monospace" color="#660000">archive_command = 'exit 0'</font></div></div></div></blockquote><div><div><br></div><div><br></div><div><br></div><div><b><u>copy-base-backup</u></b></div><div><br></div><div><div><font face="verdana, sans-serif" color="#660000">SRC_DATA=$1</font></div><div><font face="verdana, sans-serif" color="#660000">DST_HOST=$2</font></div><div><font face="verdana, sans-serif" color="#660000">DST_DATA=$3</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000"><div>PG_HOME=/var/lib/postgresql</div><div>PORT=5433</div><div>SCP=/usr/bin/scp<br></div><div>PGPOOL_LOG=/var/log/pgpool2/pgpool2.log<br></div><div>RSYNC="/usr/bin/rsync --archive --quiet --rsh=$SSH --checksum"<br></div><div>PGDIR="/etc/postgresql/9.1/main"<br></div><div><br></div></font></div><div><font face="verdana, sans-serif" color="#073763"># Delete old .WAL from my backup folder /pg_xlog_archive</font></div><div><font face="verdana, sans-serif" color="#660000">rm -f $PG_HOME/pg_xlog_archive/*</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#073763"># Stop remote PSQL node</font></div><div><font face="verdana, sans-serif" color="#660000">ssh -T $DST_HOST /etc/init.d/postgresql stop 2>/dev/null 1>/dev/null < /dev/null &</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><br></div><div><font color="#073763"><font face="verdana, sans-serif"># Replace archive_comand = 'exit 0' with = </font><span style="font-family:verdana,sans-serif">test ! -f /var/lib/postgresql/pg_xlog_archive/%f && /bin/cp %p /var/lib/postgresql/pg_xlog_archive/%f</span></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">sed -r -e "s/\s*archive_command\s*=.*/archive_command = 'test ! -f \/var\/lib\/postgresql\/pg_xlog_archive\/%f \&\& \/bin\/cp %p \/var\/lib\/postgresql\/pg_xlog_archive\/%f'/" $PGDIR/postgresql.conf > /tmp/postgresql.conf</span><br></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000"> chmod 644 /tmp/postgresql.conf</font></div><div><font face="verdana, sans-serif" color="#660000"> mv --force /tmp/postgresql.conf $PGDIR/</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">/etc/init.d/postgresql reload 2>&1 > /dev/null</font></div><div><font face="verdana, sans-serif" color="#660000">echo " Archive_command in postgresql.conf changed and reloaded." >> $PGPOOL_LOG</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">sleep 2</font></div></div><div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">psql -p $PORT -d postgres -c "select pg_start_backup('pgpool-recovery')"</span><br></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># Creating 'recovery.conf'</span><span style="color:rgb(102,0,0);font-family:verdana,sans-serif"><br></span></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">echo "restore_command = '$SCP $HOSTNAME:$PG_HOME/pg_xlog_archive/%f %p'" > $SRC_DATA/recovery.conf</span><br></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#073763"># Compress all BBDD from active PostgreSQL</font></div><div><font face="verdana, sans-serif" color="#660000">tar -C $SRC_DATA -zcf pgsql.tar.gz base global pg_clog pg_multixact pg_subtrans pg_tblspc pg_twophase pg_xlog recovery.conf</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">psql -p $PORT -d postgres -c 'select pg_stop_backup()'</span><br></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">echo " MoviNG TAR.GZ to remote node" >> $PGPOOL_LOG</font></div><div><font face="verdana, sans-serif" color="#660000">$RSYNC --remove-source-files $SRC_DATA/pgsql.tar.gz $DST_HOST:$DST_DATA</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">rm -f $SRC_DATA/recovery.conf</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">exit 0</font></div></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="arial, helvetica, sans-serif" color="#000000"><b><u>pgpool_recovery_pitr:</u></b></font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">datadir=$1</span><br></div><div><font face="verdana, sans-serif" color="#660000">DEST=$2</font></div><div><font face="verdana, sans-serif" color="#660000">DESTDIR=$3</font></div><div><font face="verdana, sans-serif" color="#660000">port=5433</font></div><div><font face="verdana, sans-serif" color="#660000">archdir=/var/lib/postgresql/pg_xlog_archive</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#073763"># Force to flush current value of sequences to xlog</font></div><div><font face="verdana, sans-serif" color="#660000">psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1|</font></div><div><font face="verdana, sans-serif" color="#660000">while read i</font></div><div><font face="verdana, sans-serif" color="#660000">do</font></div><div><font face="verdana, sans-serif" color="#660000"> if [ "$i" != "" ];then</font></div><div><font face="verdana, sans-serif" color="#660000"> psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i</font></div><div><font face="verdana, sans-serif" color="#660000"> fi</font></div><div><font face="verdana, sans-serif" color="#660000">done</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">psql -p $port -c "SELECT pgpool_switch_xlog('$archdir')" template1</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">PGDIR="/etc/postgresql/9.1/main"</span><br></div><div><font face="verdana, sans-serif" color="#660000">PGCONF="postgresql.conf"</font></div><div><font face="verdana, sans-serif" color="#660000">PGPOOL_LOG=/var/log/pgpool2/pgpool2.log</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># Replace archive_command to 'exit 0'</span><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">sed -r -e "s/\s*archive_command\s*=.*/archive_command = 'exit 0'/" $PGDIR/$PGCONF > /tmp/$PGCONF</font></div><div><font face="verdana, sans-serif" color="#660000"> chmod 644 /tmp/$PGCONF</font></div><div><font face="verdana, sans-serif" color="#660000"> mv --force /tmp/$PGCONF $PGDIR/</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">/etc/init.d/postgresql reload 2>&1 > /dev/null</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000">echo " Changed archive_command back to exit 0" >> $PGPOOL_LOG</font></div></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><font face="arial, helvetica, sans-serif" color="#000000"><b><u>pgpool_remote_start:</u></b></font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">DST_HOST=$1</span><br></div><div><font face="verdana, sans-serif" color="#660000">DST_DIR=$2</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">PGPOOL_STATUS=/var/log/pgpool2/pgpool_status</span><br></div><div><br></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># Decompress tar.gz in remote node</span><br></div><div><font face="verdana, sans-serif" color="#660000">ssh -T $DST_HOST 'cd /var/lib/postgresql/9.1/main ; tar zxf pgsql.tar.gz && rm pgsql.tar.gz' 2>/dev/null 1>/dev/null < /dev/null</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># Start postgresql in remote node with pg_ctlcluster or /etc/init.d/postgresql</span><br></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># because</span><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"> in Debian 'pg_ctl' doesn't work</span></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">PGCTL=/etc/init.d/postgresql</span><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"><br></span></div><div><font face="verdana, sans-serif" color="#660000">ssh -T $DST_HOST $PGCTL start 2>/dev/null 1>/dev/null < /dev/null &</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(7,55,99);font-family:verdana,sans-serif"># Delete pgpool_status from active node to clean up status</span><br></div><div><font face="verdana, sans-serif" color="#660000">rm -f $PGPOOL_STATUS</font></div><div><font face="verdana, sans-serif" color="#660000"><br></font></div><div><span style="color:rgb(102,0,0);font-family:verdana,sans-serif">exit 0</span><br></div></div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 8 October 2014 09:40, Michel Kirstein <span dir="ltr"><<a href="mailto:kirstein@tuhh.de" target="_blank">kirstein@tuhh.de</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Thanks, but that fix only works for sequence-values but not for the OID that is distributed to new databases. Because I'm running three nodes, I have already implemented and tested this.<br>
Did not fix the OIDs but works great for Sequences.<br>
<br>
Maybe I'm missing something...<br>
<br>
-----Ursprüngliche Nachricht-----<br>
Von: <a href="mailto:pgpool-general-bounces@pgpool.net">pgpool-general-bounces@pgpool.net</a> [mailto:<a href="mailto:pgpool-general-bounces@pgpool.net">pgpool-general-bounces@pgpool.net</a>] Im Auftrag von Jose Baez<br>
Gesendet: Mittwoch, 8. Oktober 2014 10:01<br>
An: <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
Betreff: [pgpool-general: 3213] Re: Problem creating database with replication mode (pgpool 3.1.12)<br>
<span class=""><br>
Hi, Michel!<br>
<br>
<br>
Maybe this post fixes your problem with 3 nodes:<br>
<a href="http://www.sraoss.jp/pipermail/pgpool-general/2013-November/002342.html" target="_blank">http://www.sraoss.jp/pipermail/pgpool-general/2013-November/002342.html</a><br>
<br>
<br>
<br>
On 7 October 2014 19:15, Michel Kirstein <<a href="mailto:kirstein@tuhh.de">kirstein@tuhh.de</a>> wrote:<br>
<br>
<br>
Hi Jose,<br>
i think i'm having the same problem.<br>
</span> <a href="http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html" target="_blank">http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html</a> <<a href="http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html" target="_blank">http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html</a>><br>
<div><div class="h5"><br>
I haven't found a valid solution for this yet, but seemingly stable workaround.<br>
Here is what i found so far:<br>
<br>
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.<br>
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.<br>
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.<br>
I guess this behavior is to protect a recovered database against OID collision.<br>
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.<br>
<br>
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.<br>
<br>
If you happen to find a way to fix this please share it...<br>
<br>
<br>
<br>
Am 07.10.2014 um 19:27 schrieb Jose Baez:<br>
<br>
<br>
Hello!<br>
<br>
I've got 2 virtual machine (Xen Server 6.2) with Debian Wheezy and:<br>
<br>
* PostgreSQL 9.1<br>
* Pgpool 3.1.12<br>
* Pacemaker/Corosync with pgpool-HA 2.2<br>
<br>
I tweaked the recovery scripts to do "online recovery". It went smooth<br>
both ways.<br>
<br>
However, when I "CREATE DATABASE" and connect through PgAdmin, Pgpool says:<br>
<br>
ERROR: pid 7628: read_kind_from_backend: *1 th kind C does not match<br>
with master or majority connection kind D*<br>
ERROR: pid 7628: kind mismatch among backends. Possible last query was:<br>
"SET DateStyle=ISO; SET client_min_messages=notice; SET<br>
bytea_output=escape; SELECT oid, pg_encoding_to_char(encoding) AS<br>
encoding, datlastsysoid<br>
FROM pg_database WHERE *oid = 18540" kind details are: 0[D] 1[C]*<br>
<br>
I checked database OID on each node and they are different (oid 18549<br>
and the oher is 26670). Is it normal?<br>
I created a few tables and they have indeed different OID in both<br>
nodes......<br>
<br>
If it is normal to have different OID in both nodes, why pgpool would<br>
complain?<br>
If it should not happen, why would both nodes create an object with<br>
different OID? I checked in both nodes that all Pgpool extensions are<br>
installed (regclass, insert_lock, recovery, remote_start, switch_xlog)<br>
<br>
What field/file defines the next OID to use?<br>
At first, I thought the problem was the multiple statement limitation<br>
(sent by PgAdmin) but it is not, because when I do "online recovery"<br>
both nodes get same OID in every object and I can connect with PgAdmin<br>
smoothly.<br>
<br>
(I found few links about same problem and nobody answered:<br>
</div></div> <a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html" target="_blank">http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html</a> <<a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html" target="_blank">http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html</a>><br>
<a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html" target="_blank">http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html</a> <<a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html" target="_blank">http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html</a>><br>
<span class=""><br>
I used PgAdmin from 1.16 to 1.20 with same behaviour.<br>
<br>
<br>
Thank you for your time!<br>
<br>
<br>
_pgpool.conf:_<br>
<br>
backend_hostname0 = 'psql1'<br>
backend_port0 = 5433<br>
backend_weight0 = 1<br>
backend_data_directory0 = '/var/lib/postgresql/9.1/main'<br>
backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
<br>
backend_hostname1 = 'psql2'<br>
backend_port1 = 5433<br>
backend_weight1 = 1<br>
backend_data_directory1 = '/var/lib/postgresql/9.1/main'<br>
backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
<br>
connection_cache = on<br>
<br>
replication_mode = on<br>
replicate_select = off<br>
insert_lock = on<br>
lobj_lock_table = ''<br>
<br>
load_balance_mode = off<br>
master_slave_mode = off<br>
parallel_mode = off<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
</span> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a> <<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>><br>
<span class=""><br>
<br>
<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
</span> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a> <<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>><br>
<div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</div></div></blockquote></div><br></div>