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

Поиск
Список
Период
Сортировка
От Mike Lonergan
Тема What is the accepted practice to automate initdb (PostgreSQL 9.6) toa non-default directory?
Дата
Msg-id CAEzHCEPibAGQSomzGHSr5M0VKD36v4jpPtwO6SppNpzE6FT=yQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: What is the accepted practice to automate initdb (PostgreSQL 9.6) to a non-default directory?
Список pgsql-admin
Дерево обсуждения
I'm attempting to fully script the build of a PostgreSQL server in an EC2 machine with EBS to house the default database cluster in the data_directory (aka $PGDATA?).  I'm doing this as a volunteer for HackOregon, thus I rationalize my lack of experience wrangling PostgreSQL.  My apologies up front for this lack of knowledge.

My problem: it appears that while initdb is still a shipping tool, it doesn't immediately result in a working database server, and I can't find a clean explanation in documentation (or google) of what additional commands I'm supposed to run to get the server to launch successfully.


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), 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.
When I run systemctl status postgresql.service -l  it reports:

● postgresql.service - PostgreSQL database server  Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)  Active: failed (Result: exit-code) since Thu 2018-03-01 05:41:09 UTC; 2min 8s ago Process: 3196 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=1/FAILURE)

Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal systemd[1]: Starting PostgreSQL database server...
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal postgresql-check-db-dir[3196]: Directory "/var/lib/pgsql/data" is missing or empty.
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal postgresql-check-db-dir[3196]: Use "/usr/bin/postgresql-setup --initdb"
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal postgresql-check-db-dir[3196]: to initialize the database cluster.
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal postgresql-check-db-dir[3196]: See /usr/share/doc/postgresql-9.6.6/README.rpm-dist for more information.
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal systemd[1]: postgresql.service: control process exited, code=exited status=1
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal systemd[1]: Failed to start PostgreSQL database server.
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal systemd[1]: Unit postgresql.service entered failed state.
Mar 01 05:41:09 ip-172-31-31-191.us-west-2.compute.internal systemd[1]: postgresql.service failed.
When I run sudo ls -la /var/lib/pgsql/data it reports:

total 0
drwx------ 2 postgres postgres  6 Dec 11 21:42 .
drwx------ 4 postgres postgres 54 Feb 25 21:38 ..
and when I look at /data, it contains all the files:

$ sudo ls -la /databases

total 52

drwx------ 19 postgres postgres  4096 Mar  2 16:33 .

dr-xr-xr-x 19 root     root       274 Mar  2 16:33 ..

drwx------  5 postgres postgres    41 Mar  2 16:33 base

drwx------  2 postgres postgres  4096 Mar  2 16:33 global

drwx------  2 postgres postgres    18 Mar  2 16:33 pg_clog

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_commit_ts

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_dynshmem

-rw-------  1 postgres postgres  4468 Mar  2 16:33 pg_hba.conf

-rw-------  1 postgres postgres  1636 Mar  2 16:33 pg_ident.conf

drwx------  4 postgres postgres    39 Mar  2 16:33 pg_logical

drwx------  4 postgres postgres    36 Mar  2 16:33 pg_multixact

drwx------  2 postgres postgres    18 Mar  2 16:33 pg_notify

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_replslot

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_serial

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_snapshots

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_stat

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_stat_tmp

drwx------  2 postgres postgres    18 Mar  2 16:33 pg_subtrans

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_tblspc

drwx------  2 postgres postgres     6 Mar  2 16:33 pg_twophase

-rw-------  1 postgres postgres     4 Mar  2 16:33 PG_VERSION

drwx------  3 postgres postgres    60 Mar  2 16:33 pg_xlog

-rw-------  1 postgres postgres    88 Mar  2 16:33 postgresql.auto.conf

-rw-------  1 postgres postgres 22301 Mar  2 16:33 postgresql.conf


The recommended command "postgresql-setup --initdb" doesn't seem to accept -D or --pgdata parameters, so that seems a non-starter for my scenario.

There's another hint along the way to use "postgresql-new-systemd-unit" instead, but the thin documentation seems to indicate that it's intended only for creating a second non-default database cluster, not for configuring the default cluster.


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.


Is there anywhere I can find the missing explanation for how to automate a PostgreSQL setup/config/start while placing the default database cluster in a non-default directory?

Thanks in advance for any hints you could provide.

Mike the Canuck

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Time zone issue with 10.3?
Следующее
От: Karl Denninger
Дата:
Сообщение: Re: Time zone issue with 10.3?