PostgreSQL Database Installation and Database Creation Step by Step on Linux 7

March 28, 2021

In this video, demonstrated how to install PostgreSQL on Linux and Windows Platform. The below steps to install PostgreSQL on your Linux virtual machine. Make sure you are logged in as root before you proceed for the installation.Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from Enterprise DB

To use the PostgreSQL Yum Repository, follow these steps:

Select version: 13

Select platform: Red Hat Enterprise, CentOS, Scientific or Oracle version 7

Select architecture: x86_64Copy, paste and run the relevant parts of the setup script:# Install the repository RPM:

yum install -y https://download.postgresql.org/pub/r...

# Install PostgreSQL:

yum install -y postgresql13-server

# Optionally initialize the database and enable automatic start:

/usr/pgsql-13/bin/postgresql-13-setup initdb

systemctl enable postgresql-13

systemctl start postgresql-13

Included in Distribution:

These distributions all include PostgreSQL by default. To install PostgreSQL from these repositories, use the yum command on RHEL 6 and 7:

# Install PostgreSQL:

yum install -y postgresql13-server

# Optionally initialize the database and enable automatic start:

/usr/pgsql-13/bin/postgresql-13-setup initdb

systemctl enable postgresql-13

systemctl start postgresql-13

Included in Distribution:

These distributions all include PostgreSQL by default. To install PostgreSQL from these repositories, use the yum command on RHEL 6 and 7:

Follow the following post-installation steps to create your database −

[root@srlabgg3 ~]# id postgres

uid=26(postgres) gid=26(postgres) groups=26(postgres)

[root@srlabgg3 ~]#

[root@srlabgg3 ~]# su - postgres

-bash-4.2$ createdb testdb

-bash-4.2$ psql testdb

psql (9.2.24, server 13.2)

WARNING: psql version 9.2, server version 13.0.

Some psql features might not work.

Type "help" for help.

You can start/restart postgres server in case it is not running using the following command

[root@srlabgg3 ~]# service postgresql restart

[root@srlabgg3 ol7_latest]# yum install postgresql-server

Loaded plugins: langpacks, ulninfo

ol7_UEKR4 | 2.5 kB 00:00:00

ol7_latest | 2.7 kB 00:00:00

(1/5): ol7_UEKR4/x86_64/updateinfo | 92 kB 00:00:00

(2/5): ol7_latest/x86_64/group | 660 kB 00:00:02

(3/5): ol7_latest/x86_64/updateinfo | 3.2 MB 00:00:08

(4/5): ol7_UEKR4/x86_64/primary_db | 9.2 MB 00:00:14

(5/5): ol7_latest/x86_64/primary_db | 34 MB 00:00:28

Resolving Dependencies

--> Running transaction check

---> Package postgresql-server.x86_64 0:9.2.24-4.el7_8 will be installed

--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-4.el7_8 for package: postgresql-server-9.2.24-4.el7_8.x86_64

--> Processing Dependency: postgresql(x86-64) = 9.2.24-4.el7_8 for package: postgresql-server-9.2.24-4.el7_8.x86_64

--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-9.2.24-4.el7_8.x86_64

--> Running transaction check

---> Package postgresql.x86_64 0:9.2.24-4.el7_8 will be installed

---> Package postgresql-libs.x86_64 0:9.2.24-4.el7_8 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================

Package Arch Version Repository Size

======================================================================================================================================================

Installing:

postgresql-server x86_64 9.2.24-4.el7_8 ol7_latest 3.8 M

Installing for dependencies:

postgresql x86_64 9.2.24-4.el7_8 ol7_latest 3.0 M

postgresql-libs x86_64 9.2.24-4.el7_8 ol7_latest 234 k

Transaction Summary

======================================================================================================================================================

Install 1 Package (+2 Dependent packages)

Total download size: 7.1 M

Installed size: 33 M

Is this ok [y/d/N]: y

Downloading packages:

(1/3): postgresql-libs-9.2.24-4.el7_8.x86_64.rpm | 234 kB 00:00:02

(2/3): postgresql-9.2.24-4.el7_8.x86_64.rpm | 3.0 MB 00:00:05

(3/3): postgresql-server-9.2.24-4.el7_8.x86_64.rpm | 3.8 MB 00:00:04

------------------------------------------------------------------------------------------------------------------------------------------------------

Total 1.0 MB/s | 7.1 MB 00:00:07

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : postgresql-libs-9.2.24-4.el7_8.x86_64 1/3

Installing : postgresql-9.2.24-4.el7_8.x86_64 2/3

Installing : postgresql-server-9.2.24-4.el7_8.x86_64 3/3

Verifying : postgresql-9.2.24-4.el7_8.x86_64 1/3

Verifying : postgresql-server-9.2.24-4.el7_8.x86_64 2/3

Verifying : postgresql-libs-9.2.24-4.el7_8.x86_64 3/3

Installed:

postgresql-server.x86_64 0:9.2.24-4.el7_8

Dependency Installed:

postgresql.x86_64 0:9.2.24-4.el7_8 postgresql-libs.x86_64 0:9.2.24-4.el7_8

Complete!

[root@srlabgg3 ol7_latest]# yum install -y postgresql13-server

Loaded plugins: langpacks, ulninfo

No package postgresql13-server available.

Error: Nothing to do

[root@srlabgg3 ol7_latest]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Loaded plugins: langpacks, ulninfo

pgdg-redhat-repo-latest.noarch.rpm | 6.8 kB 00:00:00

Examining /var/tmp/yum-root-pd1AJz/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-14.noarch

Marking /var/tmp/yum-root-pd1AJz/pgdg-redhat-repo-latest.noarch.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package pgdg-redhat-repo.noarch 0:42.0-14 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================

Package Arch Version Repository Size

======================================================================================================================================================

Installing:

pgdg-redhat-repo noarch 42.0-14 /pgdg-redhat-repo-latest.noarch 11 k

Transaction Summary

======================================================================================================================================================

Install 1 Package

Total size: 11 k

Installed size: 11 k

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : pgdg-redhat-repo-42.0-14.noarch 1/1

Verifying : pgdg-redhat-repo-42.0-14.noarch 1/1

Installed:

pgdg-redhat-repo.noarch 0:42.0-14

Complete!

[root@srlabgg3 ol7_latest]# yum install -y postgresql13-server

Loaded plugins: langpacks, ulninfo

pgdg-common | 2.9 kB 00:00:00

pgdg10 | 3.6 kB 00:00:00

pgdg11 | 3.6 kB 00:00:00

pgdg12 | 3.6 kB 00:00:00

pgdg13 | 3.6 kB 00:00:00

pgdg95 | 3.6 kB 00:00:00

pgdg96 | 3.6 kB 00:00:00

(1/13): pgdg10/7Server/x86_64/group_gz | 245 B 00:00:02

(2/13): pgdg12/7Server/x86_64/group_gz | 245 B 00:00:00

(3/13): pgdg-common/7Server/x86_64/primary_db | 126 kB 00:00:03

(4/13): pgdg11/7Server/x86_64/group_gz | 245 B 00:00:03

(5/13): pgdg13/7Server/x86_64/group_gz | 246 B 00:00:00

(6/13): pgdg95/7Server/x86_64/group_gz | 249 B 00:00:00

(7/13): pgdg13/7Server/x86_64/primary_db | 96 kB 00:00:00

(8/13): pgdg12/7Server/x86_64/primary_db | 188 kB 00:00:00

(9/13): pgdg95/7Server/x86_64/primary_db | 272 kB 00:00:00

(10/13): pgdg11/7Server/x86_64/primary_db | 327 kB 00:00:04

(11/13): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:00

(12/13): pgdg96/7Server/x86_64/primary_db | 303 kB 00:00:00

(13/13): pgdg10/7Server/x86_64/primary_db | 309 kB 00:00:07

Resolving Dependencies

--> Running transaction check

---> Package postgresql13-server.x86_64 0:13.2-1PGDG.rhel7 will be installed

--> Processing Dependency: postgresql13-libs(x86-64) = 13.2-1PGDG.rhel7 for package: postgresql13-server-13.2-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql13(x86-64) = 13.2-1PGDG.rhel7 for package: postgresql13-server-13.2-1PGDG.rhel7.x86_64

--> Running transaction check

---> Package postgresql13.x86_64 0:13.2-1PGDG.rhel7 will be installed

---> Package postgresql13-libs.x86_64 0:13.2-1PGDG.rhel7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================

Package Arch Version Repository Size

======================================================================================================================================================

Installing:

postgresql13-server x86_64 13.2-1PGDG.rhel7 pgdg13 5.4 M

Installing for dependencies:

postgresql13 x86_64 13.2-1PGDG.rhel7 pgdg13 1.4 M

postgresql13-libs x86_64 13.2-1PGDG.rhel7 pgdg13 379 k

Transaction Summary

======================================================================================================================================================

Install 1 Package (+2 Dependent packages)

Total download size: 7.2 M

Installed size: 30 M

Downloading packages:

warning: /var/cache/yum/x86_64/7Server/pgdg13/packages/postgresql13-libs-13.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Public key for postgresql13-libs-13.2-1PGDG.rhel7.x86_64.rpm is not installed

(1/3): postgresql13-libs-13.2-1PGDG.rhel7.x86_64.rpm | 379 kB 00:00:03

(2/3): postgresql13-13.2-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:05

(3/3): postgresql13-server-13.2-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:07

------------------------------------------------------------------------------------------------------------------------------------------------------

Total 710 kB/s | 7.2 MB 00:00:10

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Importing GPG key 0x442DF0F8:

Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"

Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8

Package : pgdg-redhat-repo-42.0-14.noarch (installed)

From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : postgresql13-libs-13.2-1PGDG.rhel7.x86_64 1/3

Installing : postgresql13-13.2-1PGDG.rhel7.x86_64 2/3

failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink

failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink

failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink

failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink

failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink

failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink

failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink

failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink

failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink

failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink

failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink

failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink

Installing : postgresql13-server-13.2-1PGDG.rhel7.x86_64 3/3

Verifying : postgresql13-server-13.2-1PGDG.rhel7.x86_64 1/3

Verifying : postgresql13-13.2-1PGDG.rhel7.x86_64 2/3

Verifying : postgresql13-libs-13.2-1PGDG.rhel7.x86_64 3/3

Installed:

postgresql13-server.x86_64 0:13.2-1PGDG.rhel7

Dependency Installed:

postgresql13.x86_64 0:13.2-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.2-1PGDG.rhel7

Complete!

[root@srlabgg3 ol7_latest]# /usr/pgsql-13/bin/postgresql-13-setup initdb

Initializing database ... OK

[root@srlabgg3 ol7_latest]# systemctl enable postgresql-13

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service.

[root@srlabgg3 ol7_latest]#

[root@srlabgg3 ol7_latest]# systemctl start postgresql-13

[root@srlabgg3 ol7_latest]#

[root@srlabgg3 ol7_latest]# systemctl status postgresql-13

● postgresql-13.service - PostgreSQL 13 database server

Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

Active: active (running) since Sat 2021-03-27 09:01:48 IST; 20s ago

Docs: https://www.postgresql.org/docs/13/static/

Process: 16719 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

Main PID: 16725 (postmaster)

CGroup: /system.slice/postgresql-13.service

├─16725 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/

├─16727 postgres: logger

├─16729 postgres: checkpointer

├─16730 postgres: background writer

├─16731 postgres: walwriter

├─16732 postgres: autovacuum launcher

├─16733 postgres: stats collector

└─16734 postgres: logical replication launcher

Mar 27 09:01:48 srlabgg3.localdomain systemd[1]: Starting PostgreSQL 13 database server...

Mar 27 09:01:48 srlabgg3.localdomain postmaster[16725]: 2021-03-27 09:01:48.542 IST [16725] LOG: redirecting log output to logging collector process

Mar 27 09:01:48 srlabgg3.localdomain postmaster[16725]: 2021-03-27 09:01:48.542 IST [16725] HINT: Future log output will appear in directory "log".

Mar 27 09:01:48 srlabgg3.localdomain systemd[1]: Started PostgreSQL 13 database server.

Hint: Some lines were ellipsized, use -l to show in full.

[root@srlabgg3 ~]# id postgres

uid=26(postgres) gid=26(postgres) groups=26(postgres)

[root@srlabgg3 ~]#

[root@srlabgg3 ~]# su - postgres

-bash-4.2$ createdb testdb

-bash-4.2$ psql testdb

psql (9.2.24, server 13.2)

WARNING: psql version 9.2, server version 13.0.

Some psql features might not work.

Type "help" for help.

testdb=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

testdb=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-----------------------------------------

---

(4 rows)

testdb=# SELECT datname FROM pg_database;

datname

-----------

postgres

testdb

template1

template0

(4 rows)

testdb=#

testdb=# SHOW config_file;

config_file

----------------------------------------

/var/lib/pgsql/13/data/postgresql.conf

(1 row)

testdb=#

testdb=# select s.name, s.setting, s.short_desc from pg_settings s where s.name='data_directory';

name | setting | short_desc

----------------+------------------------+-----------------------------------

data_directory | /var/lib/pgsql/13/data | Sets the server's data directory.

[root@srlabgg3 data]# cd /var/lib/pgsql/13/data

[root@srlabgg3 data]# ls -lrt

total 132

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_dynshmem

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_commit_ts

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_snapshots

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_serial

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_notify

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_twophase

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_tblspc

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_stat

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_replslot

drwx------. 4 postgres postgres 4096 Mar 27 09:01 pg_multixact

-rw-------. 1 postgres postgres 3 Mar 27 09:01 PG_VERSION

-rw-------. 1 postgres postgres 28015 Mar 27 09:01 postgresql.conf

-rw-------. 1 postgres postgres 88 Mar 27 09:01 postgresql.auto.conf

-rw-------. 1 postgres postgres 1636 Mar 27 09:01 pg_ident.conf

-rw-------. 1 postgres postgres 4548 Mar 27 09:01 pg_hba.conf

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_xact

drwx------. 3 postgres postgres 4096 Mar 27 09:01 pg_wal

drwx------. 2 postgres postgres 4096 Mar 27 09:01 pg_subtrans

drwx------. 2 postgres postgres 4096 Mar 27 09:01 log

-rw-------. 1 postgres postgres 30 Mar 27 09:01 current_logfiles

-rw-------. 1 postgres postgres 58 Mar 27 09:01 postmaster.opts

-rw-------. 1 postgres postgres 104 Mar 27 09:01 postmaster.pid

drwx------. 6 postgres postgres 4096 Mar 27 09:33 base

drwx------. 2 postgres postgres 4096 Mar 27 09:33 global

drwx------. 4 postgres postgres 4096 Mar 27 09:38 pg_logical

drwx------. 2 postgres postgres 4096 Mar 27 09:58 pg_stat_tmp

testdb=#

testdb=# \c postgres

psql (9.2.24, server 13.2)

WARNING: psql version 9.2, server version 13.0.

Some psql features might not work.

You are now connected to database "postgres" as user "postgres".

postgres=# \dt

No relations found.

postgres=#

postgres=# \c testdb

psql (9.2.24, server 13.2)

WARNING: psql version 9.2, server version 13.0.

Some psql features might not work.

You are now connected to database "testdb" as user "postgres".

The location of the data directory depends on the distribution. If you install from source, the default is /usr/local/pgsql/data:

ps auxw | grep postgres | grep -- -D

Below query will help to find postgres configuration file.

SHOW config_file;

PostgreSQL CREATE TABLE - ACCOUNTS table create:

CREATE TABLE accounts (

user_id serial PRIMARY KEY,

username VARCHAR ( 50 ) UNIQUE NOT NULL,

password VARCHAR ( 50 ) NOT NULL,

email VARCHAR ( 255 ) UNIQUE NOT NULL,

created_on TIMESTAMP NOT NULL,

last_login TIMESTAMP

);

The following statement creates the roles table that consists of two columns: role_id and role_name:

CREATE TABLE roles(

role_id serial PRIMARY KEY,

role_name VARCHAR (255) UNIQUE NOT NULL

);

The following statement creates the account_roles table that has three columns: user_id, role_id and grant_date.

CREATE TABLE account_roles (

user_id INT NOT NULL,

role_id INT NOT NULL,

grant_date TIMESTAMP,

PRIMARY KEY (user_id, role_id),

FOREIGN KEY (role_id)

REFERENCES roles (role_id),

FOREIGN KEY (user_id)

REFERENCES accounts (user_id)

);

PostgreSQL SELECT:

SELECT user_id,username,created_on,last_login FROM accounts;

To check Postgres Database Size:

SELECT pg_size_pretty( pg_database_size('testdb') );

=======================================================================================================================================================================

To use the PostgreSQL Yum Repository, follow these steps:

Select version:

Select platform:

Red Hat Enterprise, CentOS, Scientific or Oracle version 7

Select architecture:

x86_64

Copy, paste and run the relevant parts of the setup script:

# Install the repository RPM:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:

sudo yum install -y postgresql13-server

# Optionally initialize the database and enable automatic start:

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

sudo systemctl enable postgresql-13

sudo systemctl start postgresql-13

Included in Distribution:

These distributions all include PostgreSQL by default. To install PostgreSQL from these repositories, use the yum command on RHEL 6 and 7:

yum install postgresql-server

Post-installation Steps:

Due to policies for Red Hat family distributions, the PostgreSQL installation will not be enabled for automatic start or have the database initialized automatically. To make your database installation complete, you need to perform the following steps, based on your distribution:

For RHEL / CentOS / SL / OL 7, 8 Or Fedora 31 And Later Derived Distributions:

postgresql-setup --initdb

systemctl enable postgresql.service

systemctl start postgresql.service

Hope this helps!

Cheers!

Ramesh.

Blog: https://www.sachinrameshoracledbablog.com/

LinkedIn: https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69/

Twitter: https://twitter.com/sachinrameshdba

Facebook: https://www.facebook.com/rameshkumar.krishnamoorthy.9/

Facebook Page: https://www.facebook.com/oraclef1

Instagram: https://www.instagram.com/oraclef1_1

YouTube: https://www.youtube.com/oraclef1

YouTube: https://www.youtube.com/oracledbatutorialtamil

Sachin Ramesh Oracle DBA Blog

Stat Counter

Blog Visit Count

AdSense

Popular Posts

Labels

Social Widget

Report Abuse

Total Pageviews

Blog Archive

Tags

Social Plugin

Subscribe Us

Popular Posts

Menu Footer Widget

Sunday, March 28, 2021

PostgreSQL Database Installation and Database Creation Step by Step on Linux 7

Post a Comment: