PostgreSQL v13 Installation

Support
1

Hi folks. I can’t seem to get this thing working due to permissions issues. This is what was reported to me:

░░ A start job for unit postgresql.service has begun execution.
░░
░░ The job identifier is 2450.
Jun 29 21:28:58 server1.project33.ca postgresql-check-db-dir[2593]: cat: /var/lib/pgsql/data/PG_VERSION: Permission denied
Jun 29 21:28:58 server1.project33.ca postgresql-check-db-dir[2592]: An old version '' of the database format was found.
Jun 29 21:28:58 server1.project33.ca postgresql-check-db-dir[2592]: You need to dump and reload before using PostgreSQL 13.7.
Jun 29 21:28:58 server1.project33.ca postgresql-check-db-dir[2592]: See /usr/share/doc/postgresql/README.rpm-dist for more information.
Jun 29 21:28:58 server1.project33.ca systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ An ExecStartPre= process belonging to unit postgresql.service has exited.
░░
░░ The process' exit code is 'exited' and its exit status is 1.
Jun 29 21:28:58 server1.project33.ca systemd[1]: postgresql.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ The unit postgresql.service has entered the 'failed' state with result 'exit-code'.
Jun 29 21:28:58 server1.project33.ca systemd[1]: Failed to start PostgreSQL database server.
░░ Subject: A start job for unit postgresql.service has failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ A start job for unit postgresql.service has finished with a failure.
░░
░░ The job identifier is 2450 and the job result is failed.

Note sure if anybody has installed either 13 or 14. Any advice appreciated. Cheers

2

Check the permissions on /var/lib/pgsql. It should be owned by postgres.
Fix with … sudo chown -R postgres.postgres /var/lib/pgsql

The other error indicates you already have a database in /var/lib/pgsql. If it is not a 13.7 database (like the server you are trying to install) , you will have to dump the databases in the older version and then import it to 13.7.

3

Ya that’s what I tried changing before. Right now it’s set to postgres:postgres

drwx------.  4 postgres postgres  75 Jun 29 21:32 pgsql/

I’ve initialized the startup database somehow, and now I can’t seem to get into it.

[Thu Jun 30 12:02:11 rich@neb /var/lib] pgstart       # my script to start postgres
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to start 'postgresql.service'.
Authenticating as: Rich (rich)
Password:
==== AUTHENTICATION COMPLETE ====
Job for postgresql.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xeu postgresql.service" for details.

Here is that journalctl -xeu postgresql.service:

Jun 30 12:02:16 server1.project33.ca systemd[1]: Starting PostgreSQL database server...
░░ Subject: A start job for unit postgresql.service has begun execution
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ A start job for unit postgresql.service has begun execution.
░░
░░ The job identifier is 1292.
Jun 30 12:02:16 server1.project33.ca postgresql-check-db-dir[1684]: cat: /var/lib/pgsql/data/PG_VERSION: Permission denied
Jun 30 12:02:16 server1.project33.ca postgresql-check-db-dir[1683]: An old version '' of the database format was found.
Jun 30 12:02:16 server1.project33.ca postgresql-check-db-dir[1683]: You need to dump and reload before using PostgreSQL 13.7.
Jun 30 12:02:16 server1.project33.ca postgresql-check-db-dir[1683]: See /usr/share/doc/postgresql/README.rpm-dist for more information.
Jun 30 12:02:16 server1.project33.ca systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ An ExecStartPre= process belonging to unit postgresql.service has exited.
░░
░░ The process' exit code is 'exited' and its exit status is 1.
Jun 30 12:02:16 server1.project33.ca systemd[1]: postgresql.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ The unit postgresql.service has entered the 'failed' state with result 'exit-code'.
Jun 30 12:02:16 server1.project33.ca systemd[1]: Failed to start PostgreSQL database server.
░░ Subject: A start job for unit postgresql.service has failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ A start job for unit postgresql.service has finished with a failure.
░░
░░ The job identifier is 1292 and the job result is failed.

Everything on this machine is brand new (new box) and it’s only been 13.7. I’m thinking of a fresh install of 14.x (it is not in yum), just to plough through this.

Why is the font in here so large?

4

Since this is a new install, try this. ( as root or sudo)

  1. Stop the service. systemctl stop postgresql
  2. Delete the current database. rm -rf /var/lib/pgsl/*
  3. change ownership. chown -R postgres.postgres /var/lib/pgsql
  4. init the database. postgresql-setup --initdb
  5. Start the service. systemctl start postgresql
  6. Check status. systemctl status postgresql

I don’t about the fonts.

5

Permissions:

[Thu Jun 30 12:40:38 rich@server1 /var/lib] postgresql-setup --initdb
ERROR: The /var/lib/pgsql directory has wrong permissions.
       Please make sure the directory is writable by rich.
6

You did run that command as account “rich”. The “rich” is a regular user.

  1. Look at systemctl show postgresql.service
    There should be line that starts ExecStartPre. Does it mention /usr/bin/postgresql-check-db-dir?
  2. Isn’t that just a script? See file /usr/bin/postgresql-check-db-dir
  3. If that says “shell script”, then take a peek: cat /usr/bin/postgresql-check-db-dir
  4. I bet there is sections like:
else
    # No existing PGDATA! Warn the user to initdb it.
    echo $"\"$PGDATA\" is missing or empty."
    echo $"Use \"postgresql-setup initdb\" to initialize the database cluster."
    echo $"See $PGDOCDIR/README.rpm-dist for more information."
    exit 1
fi

and also earlier a line like PGDOCDIR=/usr/share/doc/postgresql-9.2.24. (I have version 9, so my example has that.)
5. Then read that document for more information. E.g. less /usr/share/doc/postgresql-9.2.24/README.rpm-dist
6. If it says “run as root”, then sudo postgresql-setup initdb (or whatever they say)
7. After init do check that the content has right owner (i.e. not “root”): ls -la /var/lib/pgsql
8. If not, then fix them again: sudo chown -R postgres.postgres /var/lib/pgsql

7

sudo postgresql-setup initdb

1 Like
8

OK that’s what worked. Had to get back into postgres user and create my own user and then come back out to create the database.

BTW rich is an admin.

I think I’m through. Thank you, folks!

1 Like
9

Update: Now that things are appearing normal, I cannot access the pg_hba.conf file. Error:

Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.229 EDT [2212] LOG:  starting PostgreSQL 13.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.2.1 202>
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.229 EDT [2212] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.229 EDT [2212] LOG:  listening on IPv6 address "::", port 5432
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.233 EDT [2212] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.241 EDT [2212] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.241 EDT [2212] LOG:  could not open configuration file "/usr/local/pgsql/data/pg_hba.conf": Permission den>
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.241 EDT [2212] FATAL:  could not load pg_hba.conf
Jul 02 19:47:45 server1.project33.ca postmaster[2212]: 2022-07-02 19:47:45.242 EDT [2212] LOG:  database system is shut down
Jul 02 19:47:45 server1.project33.ca systemd[1]: postgresql.service: Main process exited, code=exited, status=1/FAILURE

Here is the instructions for the --initdb:

Although initdb will attempt to create the specified data directory, it might not have permission if the parent directory of the desired data directory is root-owned. To initialize in such a setup, create an empty data directory as root, then use chown to assign ownership of that directory to the database user account, then su to become the database user to run initdb.

initdb must be run as the user that will own the server process, because the server needs to have access to the files and directories that initdb creates. Since the server cannot be run as root, you must not run initdb as root either. (It will in fact refuse to do so.)

OK, that’s really confusing. But permissions issues are everywhere. So I restart.

/usr/local/ now has no pgsql directory in it. A yum remove postgrsql-server and an installation of that package, shows no /usr/local/pgsql directory. I do the following:

mkdir pgsql
mkdir pgsql/data
sudo chown -R myuser:myuser pgsql/data
sudo su - myuser
postgresql-setup --initdb

==>
ERROR: The /var/lib/pgsql directory has wrong permissions.
       Please make sure the directory is writable by <myuser>.

pgsql has root:root ownership, and pgsql/data has myuser:myuser ownership. This is what was stated above.

The last way it was set up, I couldn’t get pg_hba.conf to be read by the server. It’s necessary.

Anyway, any input appreciated. This is confusing. Cheers

10

The postgres user should be the owner. sudo chown -R postgres.postgres /var/lib/pgsql

The user is named “postgres”…

image
image879×255 43.1 KB

image
image874×820 153 KB

11

OK that worked. But everything in the data folder isn’t available to me as an admin user. Any advice on that? I can’t get the server running as well for some reason, which isn’t mentioned:

Jul 03 17:42:56 server1.project33.ca systemd[1]: Starting PostgreSQL database server...
░░ Subject: A start job for unit postgresql.service has begun execution
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ A start job for unit postgresql.service has begun execution.
░░
░░ The job identifier is 1297.
Jul 03 17:42:56 server1.project33.ca postmaster[2058]: 2022-07-03 17:42:56.285 EDT [2058] LOG:  redirecting log output to logging collector process
Jul 03 17:42:56 server1.project33.ca postmaster[2058]: 2022-07-03 17:42:56.285 EDT [2058] HINT:  Future log output will appear in directory "log".
Jul 03 17:42:56 server1.project33.ca systemd[1]: postgresql.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ An ExecStart= process belonging to unit postgresql.service has exited.
░░
░░ The process' exit code is 'exited' and its exit status is 1.
Jul 03 17:42:56 server1.project33.ca systemd[1]: postgresql.service: Killing process 2059 (postmaster) with signal SIGKILL.
Jul 03 17:42:56 server1.project33.ca systemd[1]: postgresql.service: Killing process 2059 (postmaster) with signal SIGKILL.
Jul 03 17:42:56 server1.project33.ca systemd[1]: postgresql.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ The unit postgresql.service has entered the 'failed' state with result 'exit-code'.
Jul 03 17:42:56 server1.project33.ca systemd[1]: postgresql.service: Unit process 2059 (postmaster) remains running after unit stopped.
Jul 03 17:42:56 server1.project33.ca systemd[1]: Failed to start PostgreSQL database server.
░░ Subject: A start job for unit postgresql.service has failed
░░ Defined-By: systemd
░░ Support: https://access.redhat.com/support
░░
░░ A start job for unit postgresql.service has finished with a failure.
░░
░░ The job identifier is 1297 and the job result is failed.
12

OK, got it going again. But still, access to those directories/files is critical. I’m also finding I cannot use psql on my workstation to get into this server. I can’t find port 5432 open but the server is working.

13

Access to which directory/files? The PostgreSQL server should be the only one accessing the files that contain the data.

Yes, there are probably couple config files that you should modify now, to get everything set up correctly. It should be possible to become user ‘postgres’ via sudo. That account has the access, because you did set it as owner of the files. (One usually does use sudo to do operations as ‘root’ account, but that is not the only use for that command.)

IIRC, most SQL servers have default config that allows connections only from localhost and you need to explicitly add into the config that TCP connections from elsewhere are accepted. Might require a “user” within the database too (or was that for mysql/mariadb?).

That would be one fo those modifications in the config files that you need to do. Then, restart the service. (Which file, syntax, etc … look up from PostgreSQL’s documentation.)

You say that port is open. You have opened the port in the configuration of firewall?
If you want to know which processes do listen at some ports now, do:

sudo ss -tulpn

Or, at that specific port:

sudo ss -tulpn | grep 5432
14

The way you’ve described it, yes, the app should only have access to the data. The problem here is that config files like pg_hba.conf and postgresql.conf are also inside that directory, which I often need to access, especially during setup. I’m constantly in there. Also, the database logs are in $(PGDATA)/log. I can’t script any access by changing to a different user.

TCP is up and running fine, even on a pooling package. But this was about accessing those conf files and logs.

On my Mac, all items are owned by username:staff, which allows me to do as I like. Earlier version of postgresql, but workable all the same.

Not sure what you mean about “user” within the database. Probably not applicable.

15

Can you give read (and execute for dir) access to files to group ‘postgres’ and add the other account to group ‘postgres’? Then the other account could access those files.

1 Like
16

OK I added my user to the postgres group yesterday, but had to change the permissions to 755. Should be good to go now.

17

The 755 is quite generous, open. You had 700 for directories and 600 for files; only the owner could read (and write). Now you allow read access for everybody.

It is 750 for dirs and 640 for files that lets owner to read&write and allows the group to read. There is no reason for anyone else to have any access.

18

OK, the feedback on wrong permissions is to set it to 750. Here is what I have now:

drwx------. 7 postgres postgres   67 Jul  3 17:09 base/
drwx------. 2 postgres postgres 4.0K Jul  4 19:17 global/
drwxr-x---. 2 postgres postgres   32 Jul  3 16:48 log/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_commit_ts/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_dynshmem/
-rwxr-x---. 1 postgres postgres 4.8K Jul  3 19:42 pg_hba.conf
-rwxr-x---. 1 postgres postgres 1.6K Jul  3 16:47 pg_ident.conf
drwx------. 4 postgres postgres   68 Jul  4 19:16 pg_logical/
drwx------. 4 postgres postgres   36 Jul  3 16:47 pg_multixact/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_notify/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_replslot/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_serial/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_snapshots/
drwx------. 2 postgres postgres    6 Jul  4 19:17 pg_stat/
drwx------. 2 postgres postgres   42 Jul  4 19:17 pg_stat_tmp/
drwx------. 2 postgres postgres   18 Jul  3 16:47 pg_subtrans/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_tblspc/
drwx------. 2 postgres postgres    6 Jul  3 16:47 pg_twophase/
-rwxr-xr-x. 1 postgres postgres    3 Jul  3 16:47 PG_VERSION
drwx------. 3 postgres postgres   60 Jul  3 16:47 pg_wal/
drwx------. 2 postgres postgres   18 Jul  3 16:47 pg_xact/
-rwxr-x---. 1 postgres postgres   88 Jul  3 16:47 postgresql.auto.conf
-rwxr-x---. 1 postgres postgres  28K Jul  4 18:47 postgresql.conf
-rwxr-x---. 1 postgres postgres   45 Jul  4 19:17 postmaster.opts
-rw-r-----. 1 postgres postgres  100 Jul  4 19:17 postmaster.pid

Jul 04 19:16:15 server_n.project33.ca postmaster[2716]: 2022-07-04 19:16:15.994 EDT [2716] FATAL:  data directory "/var/lib/pgsql/data" has invalid permissions
Jul 04 19:16:15 server_n.project33.ca postmaster[2716]: 2022-07-04 19:16:15.994 EDT [2716] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
19

OK, so I’m back. I had to reinstall the OS due to some other reasons, and I’ve not changed the permissions on anything, left it as postgres. But still cannot get this to start. I’ve added my user to the postgres group.

[Mon Jul 18 08:38:12 user1@server_n ~] systemctl start postgresql.service
Failed to start postgresql.service: Access denied
See system logs and 'systemctl status postgresql.service' for details.
[Mon Jul 18 08:38:14 user1@server_n ~] systemctl status postgresql.service
○ postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
     Active: inactive (dead)

Should I start a new thread or just keep this one going?

Cheers

20

sudo systemctl start postgresql.service