Step 1: Add the PostgreSQL 9.6 Repository
# sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
Step 2: Install PostgreSQL 9.6
– To install PostgreSQL 9.6 just run the below command:
# sudo yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs -y
Step 3: Start PostgreSQL
– Initialize PostgreSQL 9.6 by executing the following command:
# /usr/pgsql-9.6/bin/postgresql96-setup initdb
– Start/Enable PostgreSQL:
# systemctl enable postgresql-9.6.service # systemctl start postgresql-9.6.service
Step 4: Accessing Database
– Switch into the postgres user:
# su – postgres
– Connect to the PostgreSQL terminal:
# psql
Step 5: Usage Examples
– List all the databases:
# \list
– Connect to a database:
# \c database_name
– List all the tables
# \d
– Create a Database
# createdb database_name # createdb database_name OWNER rolename;
– Create a table
# create table employees (name varchar(25), surname varchar(25));
– Insert records
# INSERT INTO employees VALUES ('Lotfi','waderni');
– Exit from PosgreSQL prompt:
# \q
We hope this tutorial was enough Helpful. If you need more information, or have any questions, just comment below and we will be glad to assist you!
If you like our content, please consider buying us a coffee. Thank you for your support!
17 comments
HI. Great instruction set. I ran into an error on step three, where I was required to run the initdb command PRIOR to starting the service. I didn’t know if you need to swap the two. Thanks.
Thanks Evan Cluter
I ⅼike the valuable info you provide іn your aｒticles.
I’ll bookmark yօur weblog and check again here regᥙlarly.
I’m quite sure I’ll learn а lot of new stuff right һere!
Best of luck for the next!
I am getting the following error when server is rebooted
● postgresql-9.6.service – SYSV: PostgreSQL database server.
Loaded: loaded (/etc/rc.d/init.d/postgresql-9.6; bad; vendor preset: disabled)
Active: failed (Result: exit-code) since Wed 2018-03-14 22:09:33 PDT; 5s ago
Docs: man:systemd-sysv-generator(8)
Process: 1138 ExecStart=/etc/rc.d/init.d/postgresql-9.6 start (code=exited, status=1/FAILURE)
Mar 14 22:09:31 analytics-dev-db-001.mpulse.io systemd[1]: Starting SYSV: PostgreSQL database server….
Mar 14 22:09:33 analytics-dev-db-001.mpulse.io postgresql-9.6[1138]: Starting postgresql-9.6 service: [FAILED]
Mar 14 22:09:33 analytics-dev-db-001.mpulse.io systemd[1]: postgresql-9.6.service: control process exited, code=exited status=1
Mar 14 22:09:33 analytics-dev-db-001.mpulse.io systemd[1]: Failed to start SYSV: PostgreSQL database server..
Mar 14 22:09:33 analytics-dev-db-001.mpulse.io systemd[1]: Unit postgresql-9.6.service entered failed state.
Mar 14 22:09:33 analytics-dev-db-001.mpulse.io systemd[1]: postgresql-9.6.service failed.
Hi Abhineet,
Check the log files to undrestand the causes of the issue, the failure of the service maybe caused by changing the postgresql direcotry or something else …. We are waiting for your feedback .
I am not really sure if I understand the error. When I first started the postgres, it was pointing to /var/lib/pgsql/9.6/data and that’s where the data files are present.
This is what I see further.
— Unit user-26.slice has begun shutting down.
Mar 15 11:59:49 analytics-dev-db01.dgi.com postgresql-9.6[1136]: Starting postgresql-9.6 service: [FAILED]
Mar 15 11:59:49 analytics-dev-db01.dgi.com systemd[1]: postgresql-9.6.service: control process exited, code=exited status=1
Mar 15 11:59:49 analytics-dev-db01.dgi.com systemd[1]: Failed to start SYSV: PostgreSQL database server..
— Subject: Unit postgresql-9.6.service has failed
— Defined-By: systemd
— Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
—
— Unit postgresql-9.6.service has failed.
—
— The result is failed.
Mar 15 11:59:49 analytics-dev-db01.dgi.com systemd[1]: Unit postgresql-9.6.service entered failed state.
Mar 15 11:59:49 analytics-dev-db01.dgi.com systemd[1]: postgresql-9.6.service failed.
Mar 15 11:59:49 analytics-dev-db01.dgi.com polkitd[502]: Unregistered Authentication Agent for unix-process:1130:8185 (system bus name :1.28, object path /org/freed
Mar 15 11:59:55 analytics-dev-db01.dgi.com polkitd[502]: Registered Authentication Agent for unix-process:1171:9010 (system bus name :1.31 [/usr/bin/pkttyagent –no
Mar 15 11:59:55 analytics-dev-db01.dgi.com polkitd[502]: Unregistered Authentication Agent for unix-process:1171:9010 (system bus name :1.31, object path /org/freed
Mar 15 11:59:59 analytics-dev-db01.dgi.com polkitd[502]: Registered Authentication Agent for unix-process:1177:9337 (system bus name :1.32 [/usr/bin/pkttyagent –no
Mar 15 11:59:59 analytics-dev-db01.dgi.com polkitd[502]: Unregistered Authentication Agent for unix-process:1177:9337 (system bus name :1.32, object path /org/freed
Mar 15 12:00:01 analytics-dev-db01.dgi.com systemd[1]: Started Session 5 of user root.
— Subject: Unit session-5.scope has finished start-up
— Defined-By: systemd
I found out, this is what is causing the problem. Since /run is flushed after reboot, postgres fails to start
2018-03-15 12:49:16.001 PDT > FATAL: could not create lock file “/var/run/postgresql/.s.PGSQL.5432.lock”: No such file or directory
Try to initialize the DB
I was able to fix it. Here is the resolution. I modified the init.d script for postgres and included the following lines.
vi /etc/init.d/postgresql-9.6
#Creating the lock directory – Abhineet
cd /var/run
mkdir -p postgresql
chown -R postgres:postgres /var/run/postgresql
Great job
As part of the installation Postgres adds the system user postgres and is setup to use “ident” authentication. Roles internal to Postgres (which are similar to users) match with a system user account.
***
with your tutorial, it doesnt create the user “postgres”.
How can i create the user with all the privileges that are necesary.
***
Besides, in your tutorial says:
usr/pgsql-9.6/bin/postgresql96-setup initdb
it should be
/usr/pgsql-9.6/bin/postgresql96-setup initdb
is missed a “/” at the begining.
—
Thanks for all the help
Hi,
By default, postgresql server will create a user called postgres without a password and it allows just only access localy using Unix sockets. Best practice to set a password for the user postgres. If you want to allow access using a password instead you need to modify the
pg_hba.conffile by setting the method fromidenttomd5THis dont works, it fails on step 1:
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
404 Not Found
Hi there,
try to use the below command
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmHello, I am trying to download version 9.6 but I try to install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs, it tells me that they’re not available, is there any way to solve this issue?
May 2022 – PGSQL 9.6 not not available
sudo yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs -y
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
No package postgresql96 available.
No package postgresql96-server available.
No package postgresql96-contrib available.
No package postgresql96-libs available.
Error: Nothing to do