Hello Friend's,
In this post we will discuss about the postgres software installation database creation etc.
**********************************
Steup :
OS User Access Internet connectivity
OEL 7.5 root Yes on VMware using bridge network
**********************************
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql13-server
/usr/pgsql-13/bin/postgresql-13-setup initdb
systemctl enable postgresql-13
systemctl start postgresql-13
Steps to install Postgres :
If Yum is not configured on the system follow the link to install Yum First
Link to install Yum
[root@postgresql ~]#
[root@postgresql ~]# 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-A43CXj/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-14.noarch
Marking /var/tmp/yum-root-A43CXj/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
ol7_UEKR4/x86_64 | 2.5 kB 00:00:00
ol7_UEKR4/x86_64/updateinfo | 84 kB 00:00:00
ol7_UEKR4/x86_64/primary_db | 6.4 MB 00:00:01
ol7_latest/x86_64 | 2.7 kB 00:00:00
ol7_latest/x86_64/group | 660 kB 00:00:00
ol7_latest/x86_64/updateinfo | 2.9 MB 00:00:00
ol7_latest/x86_64/primary_db | 36 MB 00:00:06
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!
Install postgresql13-server
[root@postgresql ~]# 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): pgdg11/7Server/x86_64/group_gz | 245 B 00:00:00
(2/13): pgdg10/7Server/x86_64/group_gz | 245 B 00:00:01
(3/13): pgdg12/7Server/x86_64/group_gz | 245 B 00:00:00
(4/13): pgdg13/7Server/x86_64/group_gz | 246 B 00:00:00
(5/13): pgdg-common/7Server/x86_64/primary_db | 135 kB 00:00:01
(6/13): pgdg10/7Server/x86_64/primary_db | 266 kB 00:00:01
(7/13): pgdg13/7Server/x86_64/primary_db | 47 kB 00:00:00
(8/13): pgdg95/7Server/x86_64/group_gz | 249 B 00:00:00
(9/13): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:00
(10/13): pgdg11/7Server/x86_64/primary_db | 274 kB 00:00:02
(11/13): pgdg95/7Server/x86_64/primary_db | 224 kB 00:00:00
(12/13): pgdg96/7Server/x86_64/primary_db | 255 kB 00:00:00
(13/13): pgdg12/7Server/x86_64/primary_db | 141 kB 00:00:02
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-server.x86_64 0:13.0-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.0-1PGDG.rhel7 for package: postgresql13-server-13.0-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.0-1PGDG.rhel7 for package: postgresql13-server-13.0-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql13.x86_64 0:13.0-1PGDG.rhel7 will be installed
---> Package postgresql13-libs.x86_64 0:13.0-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================
Installing:
postgresql13-server x86_64 13.0-1PGDG.rhel7 pgdg13 5.4 M
Installing for dependencies:
postgresql13 x86_64 13.0-1PGDG.rhel7 pgdg13 1.4 M
postgresql13-libs x86_64 13.0-1PGDG.rhel7 pgdg13 379 k
Transaction Summary
========================================================================================================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 7.1 M
Installed size: 30 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/pgdg13/packages/postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY 00:00:37 ETA
Public key for postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql13-libs-13.0-1PGDG.rhel7.x86_64.rpm | 379 kB 00:00:01
(2/3): postgresql13-13.0-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:04
(3/3): postgresql13-server-13.0-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:04
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.1 MB/s | 7.1 MB 00:00:06
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project "
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.0-1PGDG.rhel7.x86_64 1/3
Installing : postgresql13-13.0-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.0-1PGDG.rhel7.x86_64 3/3
Verifying : postgresql13-13.0-1PGDG.rhel7.x86_64 1/3
Verifying : postgresql13-server-13.0-1PGDG.rhel7.x86_64 2/3
Verifying : postgresql13-libs-13.0-1PGDG.rhel7.x86_64 3/3
Installed:
postgresql13-server.x86_64 0:13.0-1PGDG.rhel7
Dependency Installed:
postgresql13.x86_64 0:13.0-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.0-1PGDG.rhel7
Complete!
Initializing database
[root@postgresql ~]# /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK
Enable postgresql-13 for every reboot of server , start and check the status
[root@postgresql ~]# 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@postgresql ~]#
[root@postgresql ~]#
[root@postgresql ~]# systemctl start postgresql-13
[root@postgresql ~]#
[root@postgresql ~]# 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 Mon 2020-10-05 00:47:28 IST; 19s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 13520 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 13525 (postmaster)
CGroup: /system.slice/postgresql-13.service
├─13525 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─13529 postgres: logger
├─13531 postgres: checkpointer
├─13532 postgres: background writer
├─13533 postgres: walwriter
├─13534 postgres: autovacuum launcher
├─13535 postgres: stats collector
└─13536 postgres: logical replication launcher
Oct 05 00:47:28 postgresql.database.com systemd[1]: Starting PostgreSQL 13 database server...
Oct 05 00:47:28 postgresql.database.com postmaster[13525]: 2020-10-05 00:47:28.430 IST [13525] LOG: redirecting log output to logging collector process
Oct 05 00:47:28 postgresql.database.com postmaster[13525]: 2020-10-05 00:47:28.430 IST [13525] HINT: Future log output will appear in directory "log".
Oct 05 00:47:28 postgresql.database.com systemd[1]: Started PostgreSQL 13 database server.
Create new role for database and check newly created role
-bash-4.2$ createuser --interactive
Enter name of role to add: Newrole
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (9.2.23, server 13.0)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------+--------------------------------------------------------------
Newrole | Create role, Create DB | {}
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
Create New DB and list database in postgres :
-bash-4.2$ createdb Nwpostgredb
-bash-4.2$
-bash-4.2$ psql
psql (9.2.23, server 13.0)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
Type "help" for help.
postgres=#
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
Nwpostgredb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
postgres=# SELECT datname FROM pg_database;
datname
-------------
postgres
Nwpostgredb
template1
template0
(4 rows)
connect to perticular database in postgres
-bash-4.2$ psql -U postgres -d Nwpostgredb
psql (9.2.23, server 13.0)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
Type "help" for help.
Nwpostgredb=#
Nwpostgredb=#
Nwpostgredb=#
check file location :
Nwpostgredb=# SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
name | setting
-------------------+----------------------------------------
config_file | /var/lib/pgsql/13/data/postgresql.conf
data_directory | /var/lib/pgsql/13/data
external_pid_file |
hba_file | /var/lib/pgsql/13/data/pg_hba.conf
ident_file | /var/lib/pgsql/13/data/pg_ident.conf
(5 rows)
Hope This helps
Regards
Sultan Khan
3 comments
Click here for commentsVery nice explanation on PostgreSQL. Thanks for sharing.
ReplySuperb post
ReplyGood post clear steps
ReplyConversionConversion EmoticonEmoticon