PostgreSQL

PostgreSQL Service

systemctl status postgresql.service
service postgresql restart

PostgreSQL Config Files

/var/lib/pgsql/9.6/data/postgresql.conf
/var/lib/pgsql/9.6/data/pg_hba.conf

PostgreSQL Logs

/var/log/pgsql
/var/lib/pgsql/data/pg_log

Failed to Start

whmapi1 configureservice service=postgresql enabled=1 monitored=1

mv /usr/lib/systemd/system/postgresql.service /usr/lib/systemd/system/postgresql.servicebak

ln -s /usr/lib/systemd/system/postgresql-9.6.service /usr/lib/systemd/system/postgresql.service

systemctl daemon-reload
service postgresql restart
systemctl status postgresql

Install extensions

su - postgres
psql DBNAME
create extension pg_trgm;
\q

PostgreSQL icons missing in cPanel

ln -sf /usr/lib/systemd/system/postgresql-9.6.service /usr/lib/systemd/system/postgresql.service

systemctl daemon-reload

cd /usr/pgsql-9.6/bin

for i in *; do rm -f /usr/bin/$i ; ln -s $(pwd)/$i /usr/bin/$i; done

ln -s /var/lib/pgsql/9.6/data /var/lib/pgsql/data

/usr/local/cpanel/bin/build_global_cache

whmapi1 configureservice service=postgresql enabled=1 monitored=1

PostgreSQL SQL error

password authentication failed

psql: FATAL:  password authentication failed for user "postgres" password retrieved from file "/var/lib/pgsql/.pgpass"

Solution:
cp /var/lib/pgsql/9.6/data/pg_hba.conf /var/lib/pgsql/9.6/data/pg_hba.conf_backup
chattr -aie /var/lib/pgsql/data/pg_hba.conf
Copy the password from: cat /var/lib/pgsql/.pgpass
Login to WHM > Configure PostgreSQL and update the password. 
Then, ----ia-------e-- /var/lib/pgsql/data/pg_hba.conf

Unable to drop a user

When Unable to drop a user with error:

postgres=# drop USER australe;
ERROR:  role "australe" cannot be dropped because some objects depend on it
DETAIL:  privileges for database betterf2_futureainet

Do the following:

REASSIGN OWNED BY myuser TO postgres;
DROP OWNED BY myuser;
DROP USER [IF EXISTS] australe;

Unable to export the database

Login to server sudo su - then su - postgres psql then connect to database using

postgres=# \connect DB_NAME;

Then Run the following commands:

GRANT USAGE ON SCHEMA topology TO USER ;
GRANT SELECT ON ALL TABLES IN SCHEMA topology TO USER ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA topology TO USER ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO USER ;

GRANT USAGE ON SCHEMA public TO USER;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO USER ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO USER ;

SCRAM authentication requires libpq version 10 or above

During remote PSQL connection, sometime customer encounters following error:

Unable to connect to PostgreSQL server: SCRAM authentication requires libpq version 10 or above in /home/punkuuuc/public_html/xxx.php on line 3

Need to perform these steps on destination server:

Step 1: Open the configuration file /var/lib/pgsql/xx/data/postgresql.conf then set password_encryption = md5 (#password_encryption = scram-sha-256 # scram-sha-256 or md5) Step 2: open /var/lib/pgsql/xx/data/pg_hba.conf then change all METHOD to md5

Restart PGSQL after this step.

Step 3: Open command line (cmd,cmder,git bash...) and run psql -U postgres then enter your password when installed postgres sql Step 4: Then change your password by run ALTER USER postgres WITH PASSWORD 'new-password' in command line