[pgpool-general: 3216] Re: Problem creating database with replication mode (pgpool 3.1.12)
Jose Baez
pepote at gmail.com
Thu Oct 9 22:34:46 JST 2014
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
We both are using PostgreSQl 9.1. so* I haveI upgraded to 9.3. Same error
happens.* :((
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?
Could be my postgresql.conf or recovery scripts??
I thinkg most important fields changed from my *postgresql.conf* are:
port=5433
...
wal_level = archive
archive_mode = on
archive_command = 'exit 0'
*copy-base-backup*
SRC_DATA=$1
DST_HOST=$2
DST_DATA=$3
PG_HOME=/var/lib/postgresql
PORT=5433
SCP=/usr/bin/scp
PGPOOL_LOG=/var/log/pgpool2/pgpool2.log
RSYNC="/usr/bin/rsync --archive --quiet --rsh=$SSH --checksum"
PGDIR="/etc/postgresql/9.1/main"
# Delete old .WAL from my backup folder /pg_xlog_archive
rm -f $PG_HOME/pg_xlog_archive/*
# Stop remote PSQL node
ssh -T $DST_HOST /etc/init.d/postgresql stop 2>/dev/null 1>/dev/null <
/dev/null &
# Replace archive_comand = 'exit 0' with = test ! -f
/var/lib/postgresql/pg_xlog_archive/%f && /bin/cp %p
/var/lib/postgresql/pg_xlog_archive/%f
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
chmod 644 /tmp/postgresql.conf
mv --force /tmp/postgresql.conf $PGDIR/
/etc/init.d/postgresql reload 2>&1 > /dev/null
echo " Archive_command in postgresql.conf changed and reloaded." >>
$PGPOOL_LOG
sleep 2
psql -p $PORT -d postgres -c "select pg_start_backup('pgpool-recovery')"
# Creating 'recovery.conf'
echo "restore_command = '$SCP $HOSTNAME:$PG_HOME/pg_xlog_archive/%f %p'" >
$SRC_DATA/recovery.conf
# Compress all BBDD from active PostgreSQL
tar -C $SRC_DATA -zcf pgsql.tar.gz base global pg_clog pg_multixact
pg_subtrans pg_tblspc pg_twophase pg_xlog recovery.conf
psql -p $PORT -d postgres -c 'select pg_stop_backup()'
echo " MoviNG TAR.GZ to remote node" >> $PGPOOL_LOG
$RSYNC --remove-source-files $SRC_DATA/pgsql.tar.gz $DST_HOST:$DST_DATA
rm -f $SRC_DATA/recovery.conf
exit 0
*pgpool_recovery_pitr:*
datadir=$1
DEST=$2
DESTDIR=$3
port=5433
archdir=/var/lib/postgresql/pg_xlog_archive
# Force to flush current value of sequences to xlog
psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT
datistemplate AND datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE
relkind = 'S'" $i
fi
done
psql -p $port -c "SELECT pgpool_switch_xlog('$archdir')" template1
PGDIR="/etc/postgresql/9.1/main"
PGCONF="postgresql.conf"
PGPOOL_LOG=/var/log/pgpool2/pgpool2.log
# Replace archive_command to 'exit 0'
sed -r -e "s/\s*archive_command\s*=.*/archive_command = 'exit 0'/"
$PGDIR/$PGCONF > /tmp/$PGCONF
chmod 644 /tmp/$PGCONF
mv --force /tmp/$PGCONF $PGDIR/
/etc/init.d/postgresql reload 2>&1 > /dev/null
echo " Changed archive_command back to exit 0" >> $PGPOOL_LOG
*pgpool_remote_start:*
DST_HOST=$1
DST_DIR=$2
PGPOOL_STATUS=/var/log/pgpool2/pgpool_status
# Decompress tar.gz in remote node
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
# Start postgresql in remote node with pg_ctlcluster or
/etc/init.d/postgresql
# because in Debian 'pg_ctl' doesn't work
PGCTL=/etc/init.d/postgresql
ssh -T $DST_HOST $PGCTL start 2>/dev/null 1>/dev/null < /dev/null &
# Delete pgpool_status from active node to clean up status
rm -f $PGPOOL_STATUS
exit 0
On 8 October 2014 09:40, Michel Kirstein <kirstein at tuhh.de> wrote:
> 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.
> Did not fix the OIDs but works great for Sequences.
>
> Maybe I'm missing something...
>
> -----Ursprüngliche Nachricht-----
> Von: pgpool-general-bounces at pgpool.net [mailto:
> pgpool-general-bounces at pgpool.net] Im Auftrag von Jose Baez
> Gesendet: Mittwoch, 8. Oktober 2014 10:01
> An: pgpool-general at pgpool.net
> Betreff: [pgpool-general: 3213] Re: Problem creating database with
> replication mode (pgpool 3.1.12)
>
> Hi, Michel!
>
>
> Maybe this post fixes your problem with 3 nodes:
> http://www.sraoss.jp/pipermail/pgpool-general/2013-November/002342.html
>
>
>
> On 7 October 2014 19:15, Michel Kirstein <kirstein at tuhh.de> wrote:
>
>
> Hi Jose,
> i think i'm having the same problem.
>
> http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html
> <http://www.pgpool.net/pipermail/pgpool-general/2014-September/003172.html
> >
>
> I haven't found a valid solution for this yet, but seemingly
> stable workaround.
> 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 collision.
> 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:
>
>
> Hello!
>
> 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
> nodes......
>
> If it is normal to have different OID in both nodes, why
> pgpool would
> complain?
> 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
> smoothly.
>
> (I found few links about same problem and nobody answered:
>
> http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html
> <
> http://lists.pgfoundry.org/pipermail/pgpool-general/2009-October/002229.html
> >
>
> http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html
> <http://lists.pgfoundry.org/pipermail/pgpool-general/2008-June/001071.html
> >
>
> I used PgAdmin from 1.16 to 1.20 with same behaviour.
>
>
> Thank you for your time!
>
>
> _pgpool.conf:_
>
> 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
> http://www.pgpool.net/mailman/listinfo/pgpool-general <
> http://www.pgpool.net/mailman/listinfo/pgpool-general>
>
>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general <
> http://www.pgpool.net/mailman/listinfo/pgpool-general>
>
>
>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141009/d1da65fb/attachment-0001.html>
More information about the pgpool-general
mailing list