Qljca at 11:05, 20 June 2024

2024-06-20T11:05:31Z

← Older revision		Revision as of 11:05, 20 June 2024
Line 1:		Line 1:
			==PostgreSQL Service==
			<pre>
			systemctl status postgresql.service
			service postgresql restart
			</pre>

			==PostgreSQL Config Files==
			<pre>
			/var/lib/pgsql/9.6/data/postgresql.conf
			/var/lib/pgsql/9.6/data/pg_hba.conf
			</pre>

			==PostgreSQL Logs==
			<pre>
			/var/log/pgsql
			/var/lib/pgsql/data/pg_log
			</pre>

			==Failed to Start==
			<pre>
			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
			</pre>

			==Install extensions==
			<pre>
			su - postgres
			psql DBNAME
			create extension pg_trgm;
			\q
			</pre>

			==PostgreSQL icons missing in cPanel==
			<pre>
			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
			</pre>


	==PostgreSQL SQL error==		==PostgreSQL SQL error==

Qljca: Created page with "==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-- /v..."

2024-06-20T09:12:15Z

Created page with "==PostgreSQL SQL error== ===password authentication failed=== <pre>psql: FATAL: password authentication failed for user "postgres" password retrieved from file "/var/lib/pgsql/.pgpass"</pre> <pre> 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-- /v..."

New page

==PostgreSQL SQL error==

===password authentication failed===

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

<pre>
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
</pre>

===Unable to drop a user===

When Unable to drop a user with error:
<pre>
postgres=# drop USER australe;
ERROR: role "australe" cannot be dropped because some objects depend on it
DETAIL: privileges for database betterf2_futureainet
</pre>

Do the following:
<pre>
REASSIGN OWNED BY myuser TO postgres;
DROP OWNED BY myuser;
DROP USER [IF EXISTS] australe;
</pre>

==Unable to export the database==

Login to server
sudo su -
then su - postgres
psql
then connect to database using
<pre>postgres=# \connect DB_NAME;</pre>

Then Run the following commands:

<pre>
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 ;
</pre>

==SCRAM authentication requires libpq version 10 or above==

During remote PSQL connection, sometime customer encounters following error:
<pre>
Unable to connect to PostgreSQL server: SCRAM authentication requires libpq version 10 or above in /home/punkuuuc/public_html/xxx.php on line 3
</pre>

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

PostgreSQL - Revision history

Qljca at 11:05, 20 June 2024