Re: Ubuntu Packages / Config Files

On 2 May 2014 01:40, Stephan Fabel <sfabel(at)hawaii(dot)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(at)stuartbishop(dot)net>
http://www.stuartbishop.net/