Postgresql Error on Plesk
When trying to use Postgresql on Plesk Control Panel >> servers >> Database Servers >> Local PostgreSQL, getting the following error while using the postgresql for the first time.
Unable to rewrite /var/lib/pgsql/data/pg_hba.conf
1
Error on query to PostgreSQL
1
Unable to rewrite /var/lib/pgsql/data/pg_hba.conf
1
0: /usr/local/psa/admin/plib/DatabaseManagerPostgreSQL.php:263DatabaseManagerPostgreSQL-
>createLocalAdminLoginImpl(string ‘admin’, string ‘********’, NULL null)1: /usr/local/psa/
admin/plib/DatabaseManager.php:513DatabaseManager->updateLocalAdmin()2:
/usr/local/psa/admin/plib/DatabaseServerManager.php:229DatabaseServerManager->
updateServer(object of type DatabaseServer)3: /usr/local/psa/admin/htdocs/server/db_server_edit.php:52
This means postgresql is not running and when checking the error logs we can see like this:
#tail -f /usr/local/psa/admin/logs/httpsd_error_log
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/tmp/.s.PGSQL.5432″?
sed: can’t read /var/lib/pgsql/data/pg_hba.conf: No such file or directory
#tail -f /var/lib/pgsql/pgstartup.log
postmaster cannot access the server configuration file “/var/lib/pgsql/data/postgresql.conf”:
No such file or directory
Usually this happens when the postgresql database is not properly initialized. For fixing this error, we need to reinitialize the postgrsql database.
# rm -rf /var/lib/pgsql/data
# /etc/rc.d/init.d/postgresql start
Then again go to Plesk Control Panel >> servers >> Database Servers >> Local PostgreSQL and give your
administrator username and password.
Post PostgreSQL Installation
After installation of PostgreSQL start PostgreSQL by executing the command to see if the installation worked.
/etc/init.d/postgresql start
In the file ‘/var/lib/pgsql/data/pg_hba.conf’, replace all entries of “ident sameuser” with “trust”.
In the file ‘/var/lib/pgsql/data/postgresql.conf’, change the line “max_connections=100″ to max_connections=500″.
Restart PostgreSQL.
/etc/init.d/postgresql stop
/etc/init.d/postgresql start
How to add a user to PostgreSQL database
Following are the steps to add a user ‘testuser ‘ to the PostgreSQL database ‘testdb’ and grant access to that database;
First you need to make sure that the user is already existing in the server.
# grep testuser /etc/passwd
Else you need to create a user by type the following commands:
# adduser testuser
# passwd testuser
su - postgres
/usr/local/pgsql/bin/psql template1
/usr/local/pgsql/bin/psql -d template1 -U postgres (This command will result into the PostgreSQL command prompt)
Welcome to psql , the PostgreSQL interactive terminal.
template1=#
Now you need to add the user ‘testuser’ and give access to the database named ‘testdb’.
Follow the steps given below:
template1=# CREATE USER tom WITH PASSWORD ‘testPassword’;
template1=# CREATE DATABASE testdb;
template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;
then quit from the template1 prompt:
Now you need to login as user ‘testuser’;
@ ~]$ su - testuser
Password:
[testuser@ ~]$ /usr/local/pgsql/bin/psql -d testdb -U testuser
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=>
