On 2 May 2014 01:40, Stephan Fabel <sfabel@xxxxxxxxxx> 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@xxxxxxxxxxxxxxxx> http://www.stuartbishop.net/
