Re: What is the accepted practice to automate initdb (PostgreSQL 9.6) to a non-default directory?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mike Lonergan <mikethecanuck(at)gmail(dot)com> 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.html>), running
> sudo
> > 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
>