Thanks so much Tom, this makes a ton of sense now that you've pointed out all the things that sounded over my head when/if I bumped into them in my travails.
I'd sure prefer to not mess with the default database cluster location, but unfortunately given our deployment to AWS (on an EC2 machine for now, due to cost vs RDS), we can't rely on ephemeral storage - have to use an external EBS storage device. That means one way or another - at time of running initdb or later (before creating the first database instance), we'll have to move to a non-default data dir. [Someone suggested the use of Tablespaces to me recently, but that seems fragile - when we are considering the use of EBS 'snapshots' as well as 'pgdump' to enable a robust data preservation strategy.]
I'll definitely look into the launch script, see what I can do there. Any other thoughts folks have would be more than welcome.
Thanks!
Mike
On 2 March 2018 at 09:14, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Mike Lonergan <mikethecanuck@xxxxxxxxx> writes:
> The internet has the following pattern documented in many places (with
> varying install locations for "initdb", but always the same command)
> through at least PostgreSQL 9.3:
> sudo -u postgres postgres /opt/pg/bin/initdb -D /data/
> However while this command can complete successfully (which is encouraging,
> as it's still documented in the 9.6 docs
> <https://www.postgresql.org/docs/9.6/static/app-initdb. >), running sudohtml
> postgresql service start results in:
> Redirecting to /bin/systemctl start postgresql.service
> Job for postgresql.service failed because the control process exited
> with error code. See "systemctl status postgresql.service" and
> "journalctl -xe" for details.
Evidently you're relying on somebody's systemd unit script to launch
Postgres. Almost certainly, that unit script has the location of the
data directory hard-wired into it. If you want to use a non-default
directory location, you'll have to change the unit script (and possibly
then mutter some incantation to get systemd to notice you've changed
it; I've not worked with systemd in awhile so I forget).
> The recommended command "postgresql-setup --initdb" doesn't seem to accept
> -D or --pgdata parameters, so that seems a non-starter for my scenario.
While it's certainly possible to use a manual invocation of initdb
to get things going, when your distro provides a wrapper like
postgresql-setup you're better off using that, because it's certain
to get the data directory location, ownership, etc set up the way
the launch script expects. The reason there's no -D option is that
it scrapes the datadir location out of the launch script; again,
you *must* change that script if you're going to use this launch
method.
> I've seen various unofficial solutions that use various combinations of
> manual setup and hand-editing of .conf files, and I'm trying hard to avoid
> that so that my ops team can build and rebuild this server in an emergency
> situation without any special knowledge of the behaviour of PostgreSQL or
> its tools.
If that's what you're striving for, then the very first recommendation
would be to *not* use a non-default data dir location, but just go along
with what the distro wants to do out-of-the-box. You're just adding
another layer of complexity and things-to-go-wrong. (An example of
the sort of thing I'm worried about is that you're likely also going to
need to have a conversation with SELinux about whether the postgres
daemon is allowed to use files in the nonstandard location. It's doable,
certainly, but it's one more thing to configure.)
regards, tom lane
