Thread: Ubuntu Packages / Config Files
All, apologies if this has been addressed somewhere already. I don't have a lot of experience in PostgreSQL; this is my first setup where I'm trying to scale and provide some of the more advanced features (like WAL shipping, master-slave sync, integrating pgbouncer, etc.), and I'm looking for help regarding the configuration files. 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. The problem is that recovery.conf gets ignored in this case. I can add another symlink pointing to it into the data directory, for example, or copy the file there, then it works, but honestly this has cost me a LOT of time figuring out. So, a couple of questions: 1) am I even going about this the right way under an Ubuntu system? 2) do the packages available at apt.postgresql.org behave differently? 3) do later versions of postgresql behave differently? Eventually, I'd like to use configuration management tools like puppet to deploy something like that, but I suppose that's a topic for another day. Any pointers appreciated, Stephan
On 01/05/2014 19:40, Stephan Fabel wrote: > All, > > apologies if this has been addressed somewhere already. I don't have a > lot of experience in PostgreSQL; this is my first setup where I'm trying > to scale and provide some of the more advanced features (like WAL > shipping, master-slave sync, integrating pgbouncer, etc.), and I'm > looking for help regarding the configuration files. > > 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. You haven't made it clear that you are actually replicating to a different PostgreSQL server (whether on the same machine or on another one) - is that the case? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 05/01/2014 09:35 AM, Raymond O'Donnell wrote: > You haven't made it clear that you are actually replicating to a > different PostgreSQL server (whether on the same machine or on another > one) - is that the case? Ray. Indeed that is the case. Two servers, one master, one slave. Both identical in version: 9.1.12-0ubuntu0.12.04. Thanks, Stephan
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
On 2 May 2014 01:40, Stephan Fabel <sfabel@hawaii.edu> wrote: > All, > > apologies if this has been addressed somewhere already. I don't have a > lot of experience in PostgreSQL; this is my first setup where I'm trying > to scale and provide some of the more advanced features (like WAL > shipping, master-slave sync, integrating pgbouncer, etc.), and I'm > looking for help regarding the configuration files. > > 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. recovery.conf goes into $DATADIR, which is /var/lib/postgresql/9.1/main in your case. I rationalize this by considering it database state, rather than configuration, since commands like 'pg_ctl promote' or using a trigger file will mess with it. > The problem is that recovery.conf gets ignored in this case. I can add > another symlink pointing to it into the data directory, for example, or > copy the file there, then it works, but honestly this has cost me a LOT > of time figuring out. Having the two, with the master in /etc, will confuse you. And quite possibly PostgreSQL if it needs to promote the database. > So, a couple of questions: > > 1) am I even going about this the right way under an Ubuntu system? Yes. > 2) do the packages available at apt.postgresql.org behave differently? They are more up to date. I think the manual symlink of the SSL files in $DATADIR is no longer needed, instead pulling them from /etc. > 3) do later versions of postgresql behave differently? 8.4->9.3 are all pretty much the same. > Eventually, I'd like to use configuration management tools like puppet > to deploy something like that, but I suppose that's a topic for another day. Yeah... integration with configuration management is going to be interesting when PG allows you to modify config from the SQL command line... -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/
Stuart, thanks for your reply. On 05/08/2014 12:47 AM, Stuart Bishop wrote: > recovery.conf goes into $DATADIR, which is > /var/lib/postgresql/9.1/main in your case. I rationalize this by > considering it database state, rather than configuration, since > commands like 'pg_ctl promote' or using a trigger file will mess with > it. Fair enough; let's change the file's extension and avoid confusion then... ;-) >> The problem is that recovery.conf gets ignored in this case. I can add >> another symlink pointing to it into the data directory, for example, or >> copy the file there, then it works, but honestly this has cost me a LOT >> of time figuring out. > > Having the two, with the master in /etc, will confuse you. And quite > possibly PostgreSQL if it needs to promote the database. OK, I will leave only one copy of recovery.conf in $DATADIR to avoid problems. Thanks, Stephan