PostgreSQL DBA Basics — Install PostgreSQL 12 on Linux system
PostgreSQL is a powerful, stable, and highly configurable open-source relational database. In the article, I will go through all the necessary steps to install PostgreSQL 12 on Linux system for a single development or learning PostgreSQL environment. After finishing the installation, I will go with a few basic PostgreSQL commands for getting to know about the services.
Install PostgreSQL 12 on Linux system:
Summary steps:
Step 1: Update system and reboot the system
$ yum update -y
$ reboot
Step 2: Add PostgreSQL 12 repository for Linux 7 version
rpm -Uvh https://yum.postgresql.org/12/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Step 3: Install PostgreSQL Server and Client
yum install postgresql12-server postgresql12 -y
Step 4: Initialize Database
$ /usr/pgsql-12/bin/postgresql-12-setup initdb
Step 5: Start PostgreSQL Services
# systemctl status postgresql-12
Step 6: Enable and Start PostgreSQL Service
# systemctl enable postgresql-11.service
Step 7: Configure Firewall
# firewall-cmd — add-service=postgresql — permanent
# firewall-cmd — reload
Step 8: Setup Client authentication
i) To allow PostgreSQL to accept remote connectionschange
Change the listen to address to * in the configuration file (postgresql.conf):
listen_address = ‘*’
ii) To accept remote connections through PostgreSQL services
Change the configuration file (pg_hba.conf):
# Accept from anywhere
host all all 0.0.0.0/0 md5
So we have learned how to install PostgreSQL on Linux system. Now we will go through the installation of pgAdmin client tools.
Install pgAdmin 4 for Windows 10
pgAdmin 4 is a graphical interface for interacting with PostgreSQL databases. It is an alternative to the terminal or psql console. You can download the software and read more about at the pgAdmin
Summary steps:
i. When the pgAdmin 4 installer is up. Click Next:
ii. Next License Agreement page, select ‘I accept the agreement’. Then click next.
iii. Next, select the path for destination. then select Next.
iv. Next page — select start Menu folder
v. When installing is completed, then select ‘Launch pgAdmin 4’ and Finish.
Then, we need to set Master password.
Add a New PostgreSQL Server to pgAdmin4
i. Click Add New Server from pgAdmin main menu.
ii. Give a new connection name
iii. Click Connection tab and enter the PostgreSQL server details
iv. Press Save button to save the configuration
Few Basic Commands
Next, we can do some database level testing — here I am going to pass all the execution commands I used. For getting more in details you can go through the attached video.
Check the status DB services Status | Stop | Start | Restart
$ sudo /bin/systemctl status postgresql-10
$ sudo /bin/systemctl stop postgresql-10
$ sudo /bin/systemctl start postgresql-10
$ sudo /bin/systemctl restart postgresql-10
Create Session
++++++++++
$ sudo su — postgres
run the psql utility
$ psql
Get the list of databases
++++++++++++
postgres=# \l+
Enlisting all the available commands
+++++++++++++++++++++
i. Show help about psql command line arguments
$ psql — help
ii. The list of all the available psql commands with \?
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
— — — -the syntax of PostgreSQL statements
++++++++++++++++++++
postgres=# \h create database
Command: CREATE DATABASE
—
postgres=# \h create table
Command: CREATE TABLE
—
psql + text editor
++++++++++
If you execute the \e command, it opens the last executed command/query written in a text editor, and it also lets you edit and rerun it.
postgres=# \e
execution times of queries
+++++++++++++++
You first give the \timing command -
postgres=# \timing
Timing is on.
postgres=# SELECT version();
version
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5–28), 64-bit
(1 row)Time: 0.653 ms
postgres=# \timing
Timing is off.
List — all the database parameters
++++++++++++++++++
postgres=# show all;
Check — individual parameters
+++++++++++++++++
postgres=# show data_directory ;
data_directory
— — — — — — — — — — — —
/var/lib/pgsql/10/data
(1 row)
postgres=# show archive_mode;
archive_mode
— — — — — — —
off
(1 row)
postgres=# show shared_buffers;
shared_buffers
— — — — — — — —
128MB
(1 row)From command prompt,
postgres=# SELECT name, setting FROM pg_settings WHERE name IN (‘archive_command’,’archive_mode’);
++++++++++++++++++++
Quit Session
+++++++++++++++++++++
postgres=# \q
I hope by going through this article you a very good understanding of the process of installing and preparing PostgreSQL on the Linux system for a single development or learning PostgreSQL environments including installation of the client tool ‘pgAdmin’ for managing PostgreSQL database.
