[Postgresql] Unit postgresql.service has failed

[Pixys]

Hello,

I'm trying to run Postgresql on an Odroid-XU4 board.
I moved the database from /var/lib/postgres/data to /home/postgres/data

Here is what I have:
$this->bbcode_second_pass_code('', '$ ls -l /home/postgres
total 0
drwx------ 1 postgres postgres 456 Mar 25 21:49 data')

The database has been initialized with:
$this->bbcode_second_pass_code('', 'initdb --locale $LANG -E UTF8 -D '/home/postgres/data'')

And correctly built:
$this->bbcode_second_pass_code('', '$ sudo ls /home/postgres/data
[sudo] password for albator:
base pg_clog pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION postgresql.auto.conf
global pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_xlog postgresql.conf')

I have copied the /usr/lib/systemd/system/postgresql.service to /etc/systemd/system/postgresql.service and edited it to change the default PGROOT and PIDFile paths.

I start the service with:
$this->bbcode_second_pass_code('', '$ sudo systemctl start postgresql.service')

and I always get:
$this->bbcode_second_pass_code('', '$ sudo journalctl -xe
Mar 25 22:09:49 arcadia systemd[1]: Starting PostgreSQL database server...
-- Subject: Unit postgresql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has begun starting up.
Mar 25 22:09:49 arcadia postgres[1403]: "/home/postgres/data" is missing or empty. Use a command like
Mar 25 22:09:49 arcadia postgres[1403]: su - postgres -c "initdb --locale en_US.UTF-8 -D '/home/postgres/data'"
Mar 25 22:09:49 arcadia postgres[1403]: with relevant options, to initialize the database cluster.
Mar 25 22:09:49 arcadia systemd[1]: postgresql.service: Control process exited, code=exited status=1
Mar 25 22:09:49 arcadia systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has failed.
--
-- The result is failed.
Mar 25 22:09:49 arcadia systemd[1]: postgresql.service: Unit entered failed state.
Mar 25 22:09:49 arcadia systemd[1]: postgresql.service: Failed with result 'exit-code'.
Mar 25 22:09:49 arcadia sudo[1399]: pam_unix(sudo:session): session closed for user root
Mar 25 22:10:01 arcadia sudo[1413]: albator : TTY=pts/0 ; PWD=/home/albator ; USER=root ; COMMAND=/usr/bin/journalctl -xe
Mar 25 22:10:01 arcadia sudo[1413]: pam_unix(sudo:session): session opened for user root by albator(uid=0)')

One last thing: if I keep the data in /var/lib/postgres/data, it works.

Did I miss something?
Thanks in advance for your help!

[WarheadsSE]

can the postgres user see /home?

[Pixys]

I think so:
$this->bbcode_second_pass_code('', '[postgres@arcadia ~]$ ls /
bin dev home lost+found mnt proc run srv tmp var
boot etc lib media opt root sbin sys usr')

[Abzie]

If this is a fresh install (and you have no data in your databases), delete the dir completely, then su to postgres user, then run the initdb command again, not as root/via sudo.

[Pixys]

It does not change anything:

$this->bbcode_second_pass_code('', '# albator @ arcadia in ~ [10:09:55]
$ sudo rm -rf /home/postgres/data

# albator @ arcadia in ~ [10:10:06]
$ su - postgres
Password:
[postgres@arcadia ~]$ initdb --locale $LANG -E UTF8 -D '/home/postgres/data'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /home/postgres/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /home/postgres/data -l logfile start
')

$this->bbcode_second_pass_code('', '# albator @ arcadia in ~ [10:12:48]
$ sudo 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.

# albator @ arcadia in ~ [10:12:54] C:1
$ sudo journalctl -xe
-- Unit postgresql.service has begun starting up.
Mar 26 10:12:54 arcadia postgres[438]: "/home/postgres/data" is missing or empty. Use a command like
Mar 26 10:12:54 arcadia postgres[438]: su - postgres -c "initdb --locale en_US.UTF-8 -D '/home/postgres/data'"
Mar 26 10:12:54 arcadia postgres[438]: with relevant options, to initialize the database cluster.
Mar 26 10:12:54 arcadia systemd[1]: postgresql.service: Control process exited, code=exited status=1
Mar 26 10:12:54 arcadia systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has failed.
--
-- The result is failed.
Mar 26 10:12:54 arcadia systemd[1]: postgresql.service: Unit entered failed state.
Mar 26 10:12:54 arcadia systemd[1]: postgresql.service: Failed with result 'exit-code'.
Mar 26 10:12:54 arcadia sudo[434]: pam_unix(sudo:session): session closed for user root
Mar 26 10:13:00 arcadia sudo[449]: albator : TTY=pts/0 ; PWD=/home/albator ; USER=root ; COMMAND=/usr/bin/journalctl -xe
Mar 26 10:13:00 arcadia sudo[449]: pam_unix(sudo:session): session opened for user root by albator(uid=0)
')

And if I try this:

$this->bbcode_second_pass_code('', '[postgres@arcadia ~]$ pg_ctl -D /home/postgres/data start
server starting
[postgres@arcadia ~]$ FATAL: could not create lock file "/run/postgresql/.s.PGSQL.5432.lock": No such file or directory')

[Abzie]

Check the .service file and see if it's running under postgres user, then check the .conf to see where it creates the lock file and touch it as postgres user.

[Pixys]

Here is the .service file that runs under the postgres user:

$this->bbcode_second_pass_code('', '[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
TimeoutSec=120
User=postgres
Group=postgres

Environment=PGROOT=/home/postgres

SyslogIdentifier=postgres
PIDFile=/home/postgres/data/postmaster.pid
RuntimeDirectory=postgresql
RuntimeDirectoryMode=755

ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart= /usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120
ExecReload=/usr/bin/pg_ctl -s -D ${PGROOT}/data reload
ExecStop= /usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast

# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200

# Additional security-related features
PrivateTmp=true
ProtectHome=true
ProtectSystem=full
NoNewPrivileges=true

[Install]
WantedBy=multi-user.target
')

I've manually created /run/postgresql and gave it postgres ownership. It works in that case:
$this->bbcode_second_pass_code('', '[postgres@arcadia ~]$ pg_ctl -D /home/postgres/data start
server starting
[postgres@arcadia ~]$ LOG: database system was shut down at 2016-03-26 10:12:39 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started')

But I still can't start it with the systemd service (same error, same message)

[LuisFelipe]

As mentioned in:

https://bbs.archlinux.org/viewtopic.php ... 5#p1617785

Change "ProtectHome" to false.