Hello All,
I am trying to automate setting up a hot standby slave replication on
Ubuntu Precise, with PostgreSQL 9.1, following the Binary Replication
Tutorial: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial .
(Distribution-specific conventions: postmaster runs as a dedicated
system user named postgres; data directory is in
/var/lib/postgresql/9.1/main; most config files are in
/etc/postgresql/9.1/main.)
The current workflow is as follows:
* The server’s pg_hba.conf is set up to allow replication connections
from a specific subnet.
* Administrator logs on to the to-be slave, installs a package that
depends on postgresql-9.1, runs a shell script passing it the address of
the server.
* The script’s main part is (error handling omitted for clarity):
===
service postgresql stop
ssh postgres@$MASTER \
"psql -c \"select pg_start_backup('clone', true);\""
rsync -av --exclude pg_xlog --exclude postgresql.conf \
--exclude postmaster.pid \
-e "ssh postgres@$MASTER" $MASTER:$DATA_DIR/\* $DATA_DIR
ssh postgres@$MASTER \
"psql -c \"select pg_stop_backup();\""
rsync -av -e "ssh postgres@$MASTER" $MASTER/$DATA_DIR/pg_xlog \
$DATA_DIR/
cat >$DATA_DIR/recovery.conf <<-EOF
standby_mode = 'on'
primary_conninfo = 'host=$MASTER port=$PORT'
EOF
service postgresql start
===
This works, but leaves a bit to be desired:
* The script uses four SSH connections to the master server. In order to
avoid multiple password requests, we use public key authentication.
* Two ssh connections that run the start/stop_backup queries could be
replaced with normal psql connections as a special role granted the
rights to execute pg_start_backup and pg_stop_backup.
* rsync invocations have to have read and execute (list files) access to
the data directory on the master, and read access to individual files.
Write access is generally not necessary. However, PostgreSQL refuses to
start when the data directory is readable by anyone other than just
postgres, so we have to have rsync connect as postgres.
* Thus, the slave has an ssh key allowing superuser access to the master
database.
We would like to harden the access rights of the standby as tightly as
practical, preferably to read-only on the data directory.
I am aware that it is possible to use pg_basebackup over a replication
connection for the initial synchronization, but that way we won’t have
resume capabilities if connection drops. Additionally, I’m not sure if
--gzip applies to the network traffic on the wire or if pg_basebackup
just zips up the resulting .tar on the receiving side.
What would be the best way to limit the access rights of the slave while
retaining on-the-wire traffic compression and resumable data transfers?
--
senior developer, 2GIS Unix Team
jabber (gtalk, xmpp): yurivkhan@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
