Support #697
Updated by Daniel Curtis over 8 years ago
This is a guide for setting up pgpool2 to handle a pool of postgresql databases on Debian 8. h2. Prepare the Environment * Make sure the system is up to date: <pre> apt-get update && apt-get upgrade </pre> h2. Install Pgpool2 * Install pgpool2: <pre> apt-get install postgresql postgresql-9.4-pgpool2 rsync ssh sudo pgpool2 </pre> * Edit pg_hba.conf to enable database access via the local socket: <pre> nano /etc/postgresql/9.4/main/pg_hba.conf </pre> #* Replace peer with trust <pre> local all all trust </pre> * Edit the main postgresql config: <pre> nano /etc/postgresql/9.4/main/postgresql.conf </pre> #* And change the listen_addresses parameter: <pre> listen_addresses = '*' max_wal_senders = 1 wal_level = hot_standby archive_mode = on archive_command = 'test ! -f /var/lib/postgresql/9.4/main/archive_log/backup_in_progress || (test -f /var/lib/postgresql/9.4/main/archive_log/%f || cp %p /var/lib/postgresql/9.4/main/archive_log/%f)' </pre> * Install the archive_log command: <pre> install -o postgres -g postgres -m 700 -d /var/lib/postgresql/9.4/main/archive_log </pre> * Restart postgresql: <pre> systemctl restart postgresql </pre> * Log into the postgres database as the admin: <pre> su - postgres -c 'psql template1' </pre> #* And create the pgpool_recovery extension: <pre> CREATE EXTENSION "pgpool_recovery" \q exit </pre> * Setup local passwordless login for the postgres user <pre> su - postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa -N '' cat .ssh/id_rsa.pub >> .ssh/authorized_keys exit </pre> * Setup access remote databases with pg_basebackup non-interactively by creating a password file: <pre> su - postgres echo '*:*:*:postgres:SuperSecretPassword' > .pgpass chmod 0600 .pgpass exit </pre> * Allow the postgres user to restart postgresql without a password: <pre> echo 'postgres ALL=(ALL:ALL) NOPASSWD:/bin/systemctl start postgresql.service' >> /etc/sudoers.d/pgpool-postgres echo 'postgres ALL=(ALL:ALL) NOPASSWD:/bin/systemctl stop postgresql.service' >> /etc/sudoers.d/pgpool-postgres </pre> * Edit pg_hba.conf to enable database access via the local socket: <pre> nano /etc/postgresql/9.4/main/pg_hba.conf </pre> #* And add the postgres user replication access using md5 authentication: <pre> local all all trust host replication postgres .example.com md5 </pre> * After enabling password-based authentication, we need to set a password for the postgres user which we’ll use for making the base backup: <pre> su postgres -c psql echo "ALTER USER postgres WITH PASSWORD 'SuperSecretPassword';" </pre> h3. Configure Pgpool2 * Create a pgpool2 config from the installed examples: <pre> gunzip -c /usr/share/doc/pgpool2/examples/pgpool.conf.sample-replication.gz > /etc/pgpool2/pgpool.conf </pre> * Set a password for the pcp admin user: <pre> echo "postgres:$(pg_md5 SuperSecretPassword)" >> /etc/pgpool2/pcp.conf </pre> * In replication mode, when the client should authenticate towards the PostgreSQL database, configure pgpool2 to use password-based authentication: <pre> sed -i 's/trust$/md5/g' /etc/pgpool2/pool_hba.conf sed -i 's/\(enable_pool_hba =\) off/\1 on/g' /etc/pgpool2/pgpool.conf </pre> * Create all the usernames and passwords that will be used to connect to pgpool2: <pre> touch pool_passwd chown postgres.postgres pool_passwd pg_md5 -m -u pg2user SuperSecretPg2Password </pre> * My use case has servers on separate networks, so it is advisable to turn off load_balance_mode, otherwise queries will be sent to all healthy backends. In addition, a higher weight will be assigned to the backend which is on the same machine as pgpool2, so read-only queries are sent to the local backend only. <pre> sed -i 's/^load_balance_mode = on/load_balance_mode = off/g' /etc/pgpool2/pgpool.conf </pre> * Comment out any currently defined backends: <pre> sed -i 's/^\(backend_\)/# \1/g' /etc/pgpool2/pgpool.conf </pre> * Edit the pgpool2 config file: <pre> nano /etc/pgpool2/pgpool.conf </pre> #* And add the following to the end of the config file to define the *pg1.example.com* backend host: <pre> backend_hostname0 = 'pg1.example.com' backend_port0 = 5432 backend_weight0 = 2 backend_data_directory0 = '/var/lib/postgresql/9.4/main' </pre> #* And add the following to the end of the config file to define the *pg2.example.com* backend host: <pre> backend_hostname1 = 'pg2.example.com' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/postgresql/9.4/main' </pre> h3. Configuring recovery * Define a couple of shell scripts that handle the details of how the recovery is performed: <pre> sed -i 's/^\(recovery_\|client_idle_limit_in_recovery\)/# \1/g' /etc/pgpool2/pgpool.conf </pre> * Edit the pgpool2 config: <pre> nano /etc/pgpool2/pgpool.conf </pre> #* And add the following: <pre> recovery_user = 'postgres' recovery_password = 'SuperSecretPassword' recovery_1st_stage_command = '1st_stage.sh' recovery_2nd_stage_command = '2nd_stage.sh' client_idle_limit_in_recovery = -1 </pre> * The 1st_stage.sh script logs into the backend that should be recovered and uses pg_basebackup to copy a full backup from the master(primary) backend. It also sets up the recovery.conf which will be used by PostgreSQL when starting up: <pre> nano /var/lib/postgresql/9.4/main/1st_stage.sh </pre> #* And add the following: <pre> #!/bin/sh TS=$(date +%Y-%m-%d_%H-%M-%S) MASTER_HOST=$(hostname -f) MASTER_DATA=$1 RECOVERY_TARGET=$2 RECOVERY_DATA=$3 # Move the PostgreSQL data directory out of our way. ssh -T $RECOVERY_TARGET \ "[ -d $RECOVERY_DATA ] && mv $RECOVERY_DATA $RECOVERY_DATA.$TS" # We only use archived WAL logs during recoveries, so delete all # logs from the last recovery to limit the growth. rm $MASTER_DATA/archive_log/* # With this file present, our archive_command will actually # archive WAL files. touch $MASTER_DATA/archive_log/backup_in_progress # Perform a backup of the database. ssh -T $RECOVERY_TARGET \ "pg_basebackup -h $MASTER_HOST -D $RECOVERY_DATA --xlog" # Configure the restore_command to use the archive_log WALs we’ll copy # over in 2nd_stage.sh. echo "restore_command = 'cp $RECOVERY_DATA/archive_log/%f %p'" | \ ssh -T $RECOVERY_TARGET "cat > $RECOVERY_DATA/recovery.conf" </pre> * Create the 2nd_stage.sh script: <pre> nano /var/lib/postgresql/9.4/main/2nd_stage.sh </pre> #* And add the following: <pre> #! /bin/sh MASTER_DATA=$1 RECOVERY_TARGET=$2 RECOVERY_DATA=$3 port=5432 # 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 # Flush all transactions to disk. Since pgpool stopped all connections, # there cannot be any data that does not reside on disk until the # to-be-recovered host is back on line. psql -p $port -c "SELECT pgpool_switch_xlog('$MASTER_DATA/archive_log')" template1 # Copy over all archive logs at once. rsync -avx --delete $MASTER_DATA/archive_log/ \ $RECOVERY_TARGET:$RECOVERY_DATA/archive_log/ # Delete the flag file to disable WAL archiving again. rm $MASTER_DATA/archive_log/backup_in_progress </pre> * Also create a pgpool_remote_start script: <pre> nano /var/lib/postgresql/9.4/main/pgpool_remote_start </pre> #* And add the following: <pre> #!/bin/sh ssh $1 sudo systemctl start postgresql.service </pre> * Make the scripts executable: <pre> chmod +x /var/lib/postgresql/9.4/main/1st_stage.sh chmod +x /var/lib/postgresql/9.4/main/2nd_stage.sh chmod +x /var/lib/postgresql/9.4/main/pgpool_remote_start </pre> * Now start pgpool2 and verify that it works and can access the first node. The pcp_node_count command should return an integer number like “2”. The psql command should be able to connect and you should see your database tables when using \d. <pre> systemctl restart pgpool2.service pcp_node_count 10 localhost 9898 postgres SuperSecretPassword psql -p 5433 -U pg2user pg2db </pre> h2. Resources * http://michael.stapelberg.de/Artikel/replicated_postgresql_with_pgpool/ * http://www.pgpool.net/docs/latest/pgpool-en.html * http://cloud-engineering.forthscale.com/2013/03/how-to-install-pgpool-ii-on-postgresql.html * http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2