Re: Ubuntu Packages / Config Files - Mailing list pgsql-general

On 05/01/2014 11:40 AM, Stephan Fabel wrote:
> I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu
> packages don't put the configuration files with the cluster data (by
> default under /var/lib/postgresql/9.1/main under 12.04), but in
> /etc/postgresql/9.1/main) and they start postgres with the -c option
> pointing there.
>
> Whenever I try to add a slave, first I stop the postgresql service, move
> the above data directory to something like
> /var/lib/postgresql/9.1/main.orig, create a new 'main' directory with
> identical permissions/ownerships, and start pg_basebackup pointing
> there. It will not copy the server.crt and server.key symlinks (by
> default pointing to the "snakeoil" cert/key) so I re-create those. I
> then put the appropriate recovery.conf into /etc/postgresql/9.1/main,
> given that that's the configuration directory where everything is. I set
> "wal_level = hot_standby" and "hot_standby = on" in postgresql.conf.
> After  I then start the postgresql service again.

I built a script that works in our system to create a standby. It's nice
to have something ready-to-go and well tested when you need to quickly
get a standby up and running. I leave the script in the PostgreSQL
config directory where it's tracked, along with other config changes, in
git.

I also keep recovery.conf and the certificate files in
/etc/postgresql/9.1/main where they, too, are revision controlled and
ready to copy into place by the script.

There was some discussion some time ago about changing the requirement
to have recovery.conf and certs in the data directory but I lost track
of what was decided for post-9.1 versions.

My recovery.conf mostly consists of:
standby_mode = on
primary_conninfo = 'host=10.9.8.7 user=standby'

The script, stripped of various error-checking, confirmation screens and
other fluff basically looks like:

####################### Start
postgresql_replication_user='standby'
postgresql_owner='postgres'
master_ip='10.9.8.7'
pg_parent='/var/lib/postgresql/9.1'
data_dir="${pg_parent}/main"
backup_dir="${pg_parent}/$(date +%Y%m%d_%H%M)"
conf_dir='/etc/postgresql/9.1/main/'
server_crt='/etc/ssl/certs/ssl-cert-snakeoil.pem'
server_key='/etc/ssl/private/ssl-cert-snakeoil.key'

# Stop the server
echo "Stopping PostgreSQL"
/etc/init.d/postgresql stop

# Delete and remake the data directory
echo "Moving data directory"
mv "${data_dir}" "${backup_dir}"
mkdir "${data_dir}"
chown "${postgresql_owner}:${postgresql_owner}" "${data_dir}"
chmod 700 "${data_dir}"

# Switch to postgres user and run basebackup
echo "Re-synchronizing database from master"
su - postgres -c "
pg_basebackup \
     --pgdata \"${data_dir}\" \
     --xlog \
     --host \"${master_ip}\" \
     --user \"${postgresql_replication_user}\" \
     --verbose \
     --progress
"


# Relink the server keys
ln -s "${server_crt}" "${data_dir}/server.crt"
ln -s "${server_key}" "${data_dir}/server.key"

# Put recovery.conf into place
echo "Setting up recovery.conf"
cp -p "${conf_dir}/recovery.conf" "${data_dir}"

# Start the server
echo "Starting standby server"
/etc/init.d/postgresql start
####################### End

Hope this helps.

Cheers,
Steve