About PostgreSQL:
PostgreSQL is a powerful, open source object-relational database system. It has more than 17 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, macOS, Solaris, etc.), and Windows. It is fully ACID compliant
PostgreSQL database limits:
Limit Value
------- ------------------
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns /Table 250 - 1600 depending on column types
Maximum Indexes /Table Unlimited
To install progresql on Linux platform, you can follow below steps :
Step 1: Download postgreSQL source code:
Download the software from :
https://www.postgresql.org/ftp/source/ or Click Here
Download the required version. Here I downloaded v.11.0. Then follow below steps:
Step 2: Create postgreSQL user account
# adduser postgres
# passwd postgres
Changing password for user postgres.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
verify:
[root@example03 home]# cat /etc/passwd|grep /bin/bash
postgres:x:1006:1007::/home/postgres:/bin/bash
[root@example03 home]#
[root@example03 home]# cat /etc/group|grep postgres
postgres:x:1007:
Step 3: Install postgreSQL
PostgreSQL is a powerful, open source object-relational database system. It has more than 17 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, macOS, Solaris, etc.), and Windows. It is fully ACID compliant
PostgreSQL database limits:
Limit Value
------- ------------------
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns /Table 250 - 1600 depending on column types
Maximum Indexes /Table Unlimited
To install progresql on Linux platform, you can follow below steps :
Step 1: Download postgreSQL source code:
Download the software from :
https://www.postgresql.org/ftp/source/ or Click Here
Step 2: Create postgreSQL user account
# adduser postgres
# passwd postgres
Changing password for user postgres.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
verify:
[root@example03 home]# cat /etc/passwd|grep /bin/bash
postgres:x:1006:1007::/home/postgres:/bin/bash
[root@example03 home]#
[root@example03 home]# cat /etc/group|grep postgres
postgres:x:1007:
Step 3: Install postgreSQL
source code installation
------------------------------------------------------------
you can make that user as sudo user.
[root@example03 home]# vi /etc/sudoers
postgres ALL=(ALL) ALL
[root@example03 home]# whoami
root
[root@example03 home]# su - postgres
Last failed login: Sat Aug 10 23:09:45 PDT 2019 on pts/0
There were 4 failed login attempts since the last successful login.
[postgres@example03 ~]$ whoami
postgres
[postgres@example03 ~]$ pwd
/home/postgres
before going to install source code, we have to check /usr/local/ folder, under this folder there should not be pgsql folder. if pgsql folder is there already some one installed. make sure if pgsql folder not there, if it is there try to rename that folder or else try to install your s/w with different directory.
download the software.
click on source code.
select version
select 1st or 4th file and click on copy link address
[root@example03 pgsql]# wget https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
--2019-08-17 18:31:37-- https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
Resolving ftp.postgresql.org (ftp.postgresql.org)... 204.145.124.244, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|204.145.124.244|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 21206820 (20M) [application/x-bzip-compressed-tar]
Saving to: ‘postgresql-11.0.tar.bz2’
100%[===================================================================================================================>] 21,206,820 3.05MB/s in 12s
2019-08-17 18:31:50 (1.72 MB/s) - ‘postgresql-11.0.tar.bz2’ saved [21206820/21206820]
OR
You can download the file and do SCP to linux host;
[root@example03 pgsql]# tar -xvf postgresql-11.0.tar.bz2
[root@example03 pgsql]# cd postgresql-11.0
[root@example03 postgresql-11.0]# ls -ltr
total 740
-rw-r--r-- 1 1107 1107 1212 Oct 15 2018 README
-rw-r--r-- 1 1107 1107 1682 Oct 15 2018 Makefile
-rw-r--r-- 1 1107 1107 284 Oct 15 2018 HISTORY
-rw-r--r-- 1 1107 1107 3664 Oct 15 2018 GNUmakefile.in
-rw-r--r-- 1 1107 1107 1192 Oct 15 2018 COPYRIGHT
-rw-r--r-- 1 1107 1107 83596 Oct 15 2018 configure.in
-rwxr-xr-x 1 1107 1107 558874 Oct 15 2018 configure
-rw-r--r-- 1 1107 1107 486 Oct 15 2018 aclocal.m4
drwxrwxrwx 56 1107 1107 4096 Oct 15 2018 contrib
drwxrwxrwx 2 1107 1107 4096 Oct 15 2018 config
drwxrwxrwx 3 1107 1107 101 Oct 15 2018 doc
-rw-r--r-- 1 1107 1107 72717 Oct 15 2018 INSTALL
drwxrwxrwx 16 1107 1107 4096 Oct 15 2018 src
[root@example03 postgresql-11.0]#
[root@example03 postgresql-11.0]# ./configure --without-readline --without-zlib (this is in demo redhat)
[root@example03 postgresql-11.0]# ./configure (in realtime)
[root@example03 postgresql-11.0]# make
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
All of PostgreSQL successfully made. Ready to install.
[root@example03 postgresql-11.0]#
[root@example03 postgresql-11.0]# make install
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
PostgreSQL installation complete.
in postgreql in addition to actual they have given some more utilities with contrib module, so we have to install contrib module as well.
[root@example03 postgresql-11.0]# cd contrib
[root@example03 contrib]# make
[root@example03 contrib]# make install
------------------
validation:
-------------------
[root@example03 local]# cd pgsql/
[root@example03 pgsql]# ls -ltr
total 16
drwxr-xr-x 6 root root 4096 Aug 17 19:18 include
drwxr-xr-x 7 root root 4096 Aug 17 19:22 share
drwxr-xr-x 4 root root 4096 Aug 17 19:22 lib
drwxr-xr-x 2 root root 4096 Aug 17 19:22 bin
[root@example03 pgsql]# cd bin/
[root@example03 bin]# ls -ltr
total 11580
-rwxr-xr-x 1 root root 7905413 Aug 17 19:18 postgres
lrwxrwxrwx 1 root root 8 Aug 17 19:18 postmaster -> postgres
-rwxr-xr-x 1 root root 962424 Aug 17 19:18 ecpg
-rwxr-xr-x 1 root root 132667 Aug 17 19:18 initdb
-rwxr-xr-x 1 root root 30328 Aug 17 19:18 pg_archivecleanup
-rwxr-xr-x 1 root root 111843 Aug 17 19:18 pg_basebackup
-rwxr-xr-x 1 root root 74534 Aug 17 19:18 pg_receivewal
-rwxr-xr-x 1 root root 75333 Aug 17 19:18 pg_recvlogical
-rwxr-xr-x 1 root root 33840 Aug 17 19:18 pg_config
-rwxr-xr-x 1 root root 42812 Aug 17 19:18 pg_controldata
-rwxr-xr-x 1 root root 58294 Aug 17 19:18 pg_ctl
-rwxr-xr-x 1 root root 403980 Aug 17 19:18 pg_dump
-rwxr-xr-x 1 root root 168941 Aug 17 19:18 pg_restore
-rwxr-xr-x 1 root root 94715 Aug 17 19:18 pg_dumpall
-rwxr-xr-x 1 root root 53054 Aug 17 19:18 pg_resetwal
-rwxr-xr-x 1 root root 91500 Aug 17 19:18 pg_rewind
-rwxr-xr-x 1 root root 35221 Aug 17 19:18 pg_test_fsync
-rwxr-xr-x 1 root root 30008 Aug 17 19:18 pg_test_timing
-rwxr-xr-x 1 root root 134659 Aug 17 19:18 pg_upgrade
-rwxr-xr-x 1 root root 43526 Aug 17 19:18 pg_verify_checksums
-rwxr-xr-x 1 root root 90182 Aug 17 19:18 pg_waldump
-rwxr-xr-x 1 root root 158854 Aug 17 19:18 pgbench
-rwxr-xr-x 1 root root 461604 Aug 17 19:18 psql
-rwxr-xr-x 1 root root 63466 Aug 17 19:18 createdb
-rwxr-xr-x 1 root root 58814 Aug 17 19:18 dropdb
-rwxr-xr-x 1 root root 64143 Aug 17 19:18 createuser
-rwxr-xr-x 1 root root 58784 Aug 17 19:18 dropuser
-rwxr-xr-x 1 root root 63646 Aug 17 19:18 clusterdb
-rwxr-xr-x 1 root root 68462 Aug 17 19:18 vacuumdb
-rwxr-xr-x 1 root root 63742 Aug 17 19:18 reindexdb
-rwxr-xr-x 1 root root 58799 Aug 17 19:18 pg_isready
-rwxr-xr-x 1 root root 29914 Aug 17 19:22 oid2name
-rwxr-xr-x 1 root root 30494 Aug 17 19:22 pg_standby
-rwxr-xr-x 1 root root 29821 Aug 17 19:22 vacuumlo
[root@example03 bin]#
in source code by default you will not get any defualt configuration files or data file. To generate all the default files we have to perform database initialization by using initdb utility.
To generate default files we need specific diretory, we call that directory as DATA directory.
[root@example03 bin]# mkdir /usr/pgsql
to work with postgresql, all the folders related postgresql should be owned by postgres user.
[root@example03 pgsql]# chown -R postgres:postgres /usr/local/pgsql/
[root@example03 pgsql]# chown -R postgres:postgres /usr/pgsql/
if you want to work with any postgres utility you should run the command with postgres user only.
[root@example03 pgsql]# su - postgres
Last login: Sat Aug 17 18:23:06 PDT 2019 on pts/0
[postgres@example03 ~]$ whoami
postgres
[postgres@example03 ~]$ /usr/local/pgsql/bin/initdb -D /usr/pgsql/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /usr/pgsql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ -l logfile start
[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 48
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_twophase
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_tblspc
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_stat
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_snapshots
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_serial
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_replslot
drwx------ 4 postgres postgres 34 Aug 17 19:31 pg_multixact
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_dynshmem
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_commit_ts
-rw------- 1 postgres postgres 3 Aug 17 19:31 PG_VERSION
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_stat_tmp
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf
-rw------- 1 postgres postgres 88 Aug 17 19:31 postgresql.auto.conf
-rw------- 1 postgres postgres 1636 Aug 17 19:31 pg_ident.conf
-rw------- 1 postgres postgres 4513 Aug 17 19:31 pg_hba.conf
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_xact
drwx------ 3 postgres postgres 58 Aug 17 19:31 pg_wal
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_subtrans
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_notify
drwx------ 2 postgres postgres 4096 Aug 17 19:31 global
drwx------ 5 postgres postgres 38 Aug 17 19:31 base
drwx------ 4 postgres postgres 65 Aug 17 19:31 pg_logical
[postgres@example03 pgsql]$
start and stop services:
--------------------------------
To stop and start postgresql services we have an utility called pg_ctl.
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: no server running
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:33:36.642 PDT [14521] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:33:36.642 PDT [14521] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:33:36.645 PDT [14521] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:33:36.655 PDT [14522] LOG: database system was shut down at 2019-08-17 19:31:15 PDT
2019-08-17 19:33:36.657 PDT [14521] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: server is running (PID: 14521)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[postgres@example03 pgsql]$ ps -ef|grep postgres
root 14457 2204 0 19:30 pts/0 00:00:00 su - postgres
postgres 14458 14457 0 19:30 pts/0 00:00:00 -bash
postgres 14521 1 0 19:33 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/pgsql
postgres 14523 14521 0 19:33 ? 00:00:00 postgres: checkpointer
postgres 14524 14521 0 19:33 ? 00:00:00 postgres: background writer
postgres 14525 14521 0 19:33 ? 00:00:00 postgres: walwriter
postgres 14526 14521 0 19:33 ? 00:00:00 postgres: autovacuum launcher
postgres 14527 14521 0 19:33 ? 00:00:00 postgres: stats collector
postgres 14528 14521 0 19:33 ? 00:00:00 postgres: logical replication launcher
postgres 14530 14458 0 19:33 pts/0 00:00:00 ps -ef
postgres 14531 14458 0 19:33 pts/0 00:00:00 grep --color=auto postgres
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop
waiting for server to shut down....2019-08-17 19:34:12.429 PDT [14521] LOG: received fast shutdown request
2019-08-17 19:34:12.433 PDT [14521] LOG: aborting any active transactions
2019-08-17 19:34:12.436 PDT [14521] LOG: background worker "logical replication launcher" (PID 14528) exited with exit code 1
2019-08-17 19:34:12.436 PDT [14523] LOG: shutting down
2019-08-17 19:34:12.446 PDT [14521] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$ ps -ef|grep postgres
root 14457 2204 0 19:30 pts/0 00:00:00 su - postgres
postgres 14458 14457 0 19:30 pts/0 00:00:00 -bash
postgres 14533 14458 0 19:34 pts/0 00:00:00 ps -ef
postgres 14534 14458 0 19:34 pts/0 00:00:00 grep --color=auto postgres
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:34:21.739 PDT [14537] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:21.739 PDT [14537] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:21.742 PDT [14537] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:21.754 PDT [14538] LOG: database system was shut down at 2019-08-17 19:34:12 PDT
2019-08-17 19:34:21.757 PDT [14537] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ reload
server signaled
2019-08-17 19:34:29.474 PDT [14537] LOG: received SIGHUP, reloading configuration files
[postgres@example03 pgsql]$
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ restart
waiting for server to shut down....2019-08-17 19:34:58.743 PDT [14537] LOG: received fast shutdown request
2019-08-17 19:34:58.745 PDT [14537] LOG: aborting any active transactions
2019-08-17 19:34:58.747 PDT [14537] LOG: background worker "logical replication launcher" (PID 14544) exited with exit code 1
2019-08-17 19:34:58.747 PDT [14539] LOG: shutting down
2019-08-17 19:34:58.756 PDT [14537] LOG: database system is shut down
done
server stopped
waiting for server to start....2019-08-17 19:34:58.851 PDT [14547] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:58.851 PDT [14547] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:58.854 PDT [14547] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:58.865 PDT [14548] LOG: database system was shut down at 2019-08-17 19:34:58 PDT
2019-08-17 19:34:58.869 PDT [14547] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$
Note : default mode is fast shut down.
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m smart
waiting for server to shut down....2019-08-17 19:37:31.428 PDT [14547] LOG: received smart shutdown request
2019-08-17 19:37:31.432 PDT [14547] LOG: background worker "logical replication launcher" (PID 14554) exited with exit code 1
2019-08-17 19:37:31.432 PDT [14549] LOG: shutting down
2019-08-17 19:37:31.441 PDT [14547] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:37:45.214 PDT [14562] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:37:45.214 PDT [14562] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:37:45.216 PDT [14562] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:37:45.226 PDT [14563] LOG: database system was shut down at 2019-08-17 19:37:31 PDT
2019-08-17 19:37:45.229 PDT [14562] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m immediate
waiting for server to shut down....2019-08-17 19:37:55.603 PDT [14562] LOG: received immediate shutdown request
2019-08-17 19:37:55.605 PDT [14567] WARNING: terminating connection because of crash of another server process
2019-08-17 19:37:55.605 PDT [14567] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-08-17 19:37:55.605 PDT [14567] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2019-08-17 19:37:55.607 PDT [14562] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$
how to enable services auto start:
----------------------------------------------
[root@example03 lib]# cd /var/lib/pgsql/postgresql-11.0/contrib/start-scripts/
[root@example03 start-scripts]# cp linux /etc/init.d/postgresql10
[root@example03 start-scripts]# chmod 775 /etc/init.d/postgresql10
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14573)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 stop
Stopping PostgreSQL: 2019-08-17 19:43:12.244 PDT [14573] LOG: received fast shutdown request
2019-08-17 19:43:12.249 PDT [14573] LOG: aborting any active transactions
2019-08-17 19:43:12.253 PDT [14573] LOG: background worker "logical replication launcher" (PID 14580) exited with exit code 1
2019-08-17 19:43:12.253 PDT [14575] LOG: shutting down
2019-08-17 19:43:12.267 PDT [14573] LOG: database system is shut down
ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: no server running
[root@example03 start-scripts]# /etc/init.d/postgresql10 start
Starting PostgreSQL: ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14845)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#
in the file fill proper installation location and data directory location.
[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 60
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_tblspc:all the user created tablespaces information will be recorded here.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_snapshots: if you create any snapshots, that information will be recorded here, just awr report.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_replslot: during the replication setup we will disucss
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_dynshmem: shared memory dynamic allocation information will be recorded here.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_commit_ts: all the commited transaction infromation will be recorded here
-rw------- 1 postgres postgres 3 Aug 17 19:31 PG_VERSION: it will displays the version
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf --complete parameter managed by this file
-rw------- 1 postgres postgres 88 Aug 17 19:31 postgresql.auto.conf: if you are setting parameter by using alter system set command, those entries will be recorded here.
-rw------- 1 postgres postgres 1636 Aug 17 19:31 pg_ident.conf: some times people will try to conenct db with ldap account, in that case that user details we have to mention here.
-rw------- 1 postgres postgres 4513 Aug 17 19:31 pg_hba.conf:to prevent/manage remote connections
drwx------ 2 postgres postgres 17 Aug 17 19:3pg_Wal : all the wal files will be recorded here.
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_subtrans: as part of joins, temp tables creations more subtransactions will happens, that data will be managed here.
drwx------ 2 postgres postgres 4096 Aug 17 19:31 global:only global obejcts infromation will be stored here (users, user access rigths, tablesapces)
drwx------ 5 postgres postgres 38 Aug 17 19:31 base: our objects actual data (tables,materilized views,database)
drwx------ 4 postgres postgres 65 Aug 17 19:43 pg_logical: as part of logical replication data will be stored here.
-rw------- 1 postgres postgres 48 Aug 17 19:43 postmaster.opts
[postgres@example03 pgsql]$ cat postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
drwx------ 2 postgres postgres 17 Aug 17 19:43 pg_notify: during server start up some times we will get error messages with hint, that notification data will be stored here.
-rw-rw-r-- 1 postgres postgres 705 Aug 17 19:43 serverlog : all the activites will be recorded here.
[postgres@example03 pgsql]$ cat serverlog
2019-08-17 19:43:19.082 PDT [14845] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:43:19.082 PDT [14845] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:43:19.084 PDT [14845] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:43:19.099 PDT [14846] LOG: database system was shut down at 2019-08-17 19:43:12 PDT
2019-08-17 19:43:19.109 PDT [14845] LOG: database system is ready to accept connections
[postgres@example03 pgsql]$
-rw------- 1 postgres postgres 77 Aug 17 19:43 postmaster.pid
[postgres@example03 pgsql]$ cat postmaster.pid
14845
/usr/pgsql
1566096199
5432
/tmp
localhost
5432001 294912
ready
[postgres@example03 pgsql]$ ps -ef|grep 14845
postgres 14845 1 0 19:43 ? 00:00:00 /usr/local/pgsql/bin/postmaster -D /usr/pgsql
postgres 14847 14845 0 19:43 ? 00:00:00 postgres: checkpointer
postgres 14848 14845 0 19:43 ? 00:00:00 postgres: background writer
postgres 14849 14845 0 19:43 ? 00:00:00 postgres: walwriter
postgres 14850 14845 0 19:43 ? 00:00:00 postgres: autovacuum launcher
postgres 14851 14845 0 19:43 ? 00:00:00 postgres: stats collector
postgres 14852 14845 0 19:43 ? 00:00:00 postgres: logical replication launcher
postgres 14957 14875 0 19:59 pts/0 00:00:00 grep --color=auto 14845
[postgres@example03 pgsql]$
drwx------ 2 postgres postgres 6 Aug 17 19:43 pg_stat: all the statistics will be stored here.
drwx------ 2 postgres postgres 24 Aug 17 19:44 pg_stat_tmp:temp statistics, for which database how much size of temp files created, how many temp files created.
Click here to proceed connect database with user and manager user in database
------------------------------------------------------------
you can make that user as sudo user.
[root@example03 home]# vi /etc/sudoers
postgres ALL=(ALL) ALL
[root@example03 home]# whoami
root
[root@example03 home]# su - postgres
Last failed login: Sat Aug 10 23:09:45 PDT 2019 on pts/0
There were 4 failed login attempts since the last successful login.
[postgres@example03 ~]$ whoami
postgres
[postgres@example03 ~]$ pwd
/home/postgres
before going to install source code, we have to check /usr/local/ folder, under this folder there should not be pgsql folder. if pgsql folder is there already some one installed. make sure if pgsql folder not there, if it is there try to rename that folder or else try to install your s/w with different directory.
download the software.
click on source code.
select version
select 1st or 4th file and click on copy link address
[root@example03 pgsql]# wget https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
--2019-08-17 18:31:37-- https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
Resolving ftp.postgresql.org (ftp.postgresql.org)... 204.145.124.244, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|204.145.124.244|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 21206820 (20M) [application/x-bzip-compressed-tar]
Saving to: ‘postgresql-11.0.tar.bz2’
100%[===================================================================================================================>] 21,206,820 3.05MB/s in 12s
2019-08-17 18:31:50 (1.72 MB/s) - ‘postgresql-11.0.tar.bz2’ saved [21206820/21206820]
OR
You can download the file and do SCP to linux host;
[root@example03 pgsql]# tar -xvf postgresql-11.0.tar.bz2
[root@example03 pgsql]# cd postgresql-11.0
[root@example03 postgresql-11.0]# ls -ltr
total 740
-rw-r--r-- 1 1107 1107 1212 Oct 15 2018 README
-rw-r--r-- 1 1107 1107 1682 Oct 15 2018 Makefile
-rw-r--r-- 1 1107 1107 284 Oct 15 2018 HISTORY
-rw-r--r-- 1 1107 1107 3664 Oct 15 2018 GNUmakefile.in
-rw-r--r-- 1 1107 1107 1192 Oct 15 2018 COPYRIGHT
-rw-r--r-- 1 1107 1107 83596 Oct 15 2018 configure.in
-rwxr-xr-x 1 1107 1107 558874 Oct 15 2018 configure
-rw-r--r-- 1 1107 1107 486 Oct 15 2018 aclocal.m4
drwxrwxrwx 56 1107 1107 4096 Oct 15 2018 contrib
drwxrwxrwx 2 1107 1107 4096 Oct 15 2018 config
drwxrwxrwx 3 1107 1107 101 Oct 15 2018 doc
-rw-r--r-- 1 1107 1107 72717 Oct 15 2018 INSTALL
drwxrwxrwx 16 1107 1107 4096 Oct 15 2018 src
[root@example03 postgresql-11.0]#
[root@example03 postgresql-11.0]# ./configure --without-readline --without-zlib (this is in demo redhat)
[root@example03 postgresql-11.0]# ./configure (in realtime)
[root@example03 postgresql-11.0]# make
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
All of PostgreSQL successfully made. Ready to install.
[root@example03 postgresql-11.0]#
[root@example03 postgresql-11.0]# make install
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
PostgreSQL installation complete.
in postgreql in addition to actual they have given some more utilities with contrib module, so we have to install contrib module as well.
[root@example03 postgresql-11.0]# cd contrib
[root@example03 contrib]# make
[root@example03 contrib]# make install
------------------
validation:
-------------------
[root@example03 local]# cd pgsql/
[root@example03 pgsql]# ls -ltr
total 16
drwxr-xr-x 6 root root 4096 Aug 17 19:18 include
drwxr-xr-x 7 root root 4096 Aug 17 19:22 share
drwxr-xr-x 4 root root 4096 Aug 17 19:22 lib
drwxr-xr-x 2 root root 4096 Aug 17 19:22 bin
[root@example03 pgsql]# cd bin/
[root@example03 bin]# ls -ltr
total 11580
-rwxr-xr-x 1 root root 7905413 Aug 17 19:18 postgres
lrwxrwxrwx 1 root root 8 Aug 17 19:18 postmaster -> postgres
-rwxr-xr-x 1 root root 962424 Aug 17 19:18 ecpg
-rwxr-xr-x 1 root root 132667 Aug 17 19:18 initdb
-rwxr-xr-x 1 root root 30328 Aug 17 19:18 pg_archivecleanup
-rwxr-xr-x 1 root root 111843 Aug 17 19:18 pg_basebackup
-rwxr-xr-x 1 root root 74534 Aug 17 19:18 pg_receivewal
-rwxr-xr-x 1 root root 75333 Aug 17 19:18 pg_recvlogical
-rwxr-xr-x 1 root root 33840 Aug 17 19:18 pg_config
-rwxr-xr-x 1 root root 42812 Aug 17 19:18 pg_controldata
-rwxr-xr-x 1 root root 58294 Aug 17 19:18 pg_ctl
-rwxr-xr-x 1 root root 403980 Aug 17 19:18 pg_dump
-rwxr-xr-x 1 root root 168941 Aug 17 19:18 pg_restore
-rwxr-xr-x 1 root root 94715 Aug 17 19:18 pg_dumpall
-rwxr-xr-x 1 root root 53054 Aug 17 19:18 pg_resetwal
-rwxr-xr-x 1 root root 91500 Aug 17 19:18 pg_rewind
-rwxr-xr-x 1 root root 35221 Aug 17 19:18 pg_test_fsync
-rwxr-xr-x 1 root root 30008 Aug 17 19:18 pg_test_timing
-rwxr-xr-x 1 root root 134659 Aug 17 19:18 pg_upgrade
-rwxr-xr-x 1 root root 43526 Aug 17 19:18 pg_verify_checksums
-rwxr-xr-x 1 root root 90182 Aug 17 19:18 pg_waldump
-rwxr-xr-x 1 root root 158854 Aug 17 19:18 pgbench
-rwxr-xr-x 1 root root 461604 Aug 17 19:18 psql
-rwxr-xr-x 1 root root 63466 Aug 17 19:18 createdb
-rwxr-xr-x 1 root root 58814 Aug 17 19:18 dropdb
-rwxr-xr-x 1 root root 64143 Aug 17 19:18 createuser
-rwxr-xr-x 1 root root 58784 Aug 17 19:18 dropuser
-rwxr-xr-x 1 root root 63646 Aug 17 19:18 clusterdb
-rwxr-xr-x 1 root root 68462 Aug 17 19:18 vacuumdb
-rwxr-xr-x 1 root root 63742 Aug 17 19:18 reindexdb
-rwxr-xr-x 1 root root 58799 Aug 17 19:18 pg_isready
-rwxr-xr-x 1 root root 29914 Aug 17 19:22 oid2name
-rwxr-xr-x 1 root root 30494 Aug 17 19:22 pg_standby
-rwxr-xr-x 1 root root 29821 Aug 17 19:22 vacuumlo
[root@example03 bin]#
in source code by default you will not get any defualt configuration files or data file. To generate all the default files we have to perform database initialization by using initdb utility.
To generate default files we need specific diretory, we call that directory as DATA directory.
[root@example03 bin]# mkdir /usr/pgsql
to work with postgresql, all the folders related postgresql should be owned by postgres user.
[root@example03 pgsql]# chown -R postgres:postgres /usr/local/pgsql/
[root@example03 pgsql]# chown -R postgres:postgres /usr/pgsql/
if you want to work with any postgres utility you should run the command with postgres user only.
[root@example03 pgsql]# su - postgres
Last login: Sat Aug 17 18:23:06 PDT 2019 on pts/0
[postgres@example03 ~]$ whoami
postgres
[postgres@example03 ~]$ /usr/local/pgsql/bin/initdb -D /usr/pgsql/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /usr/pgsql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ -l logfile start
[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 48
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_twophase
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_tblspc
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_stat
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_snapshots
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_serial
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_replslot
drwx------ 4 postgres postgres 34 Aug 17 19:31 pg_multixact
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_dynshmem
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_commit_ts
-rw------- 1 postgres postgres 3 Aug 17 19:31 PG_VERSION
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_stat_tmp
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf
-rw------- 1 postgres postgres 88 Aug 17 19:31 postgresql.auto.conf
-rw------- 1 postgres postgres 1636 Aug 17 19:31 pg_ident.conf
-rw------- 1 postgres postgres 4513 Aug 17 19:31 pg_hba.conf
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_xact
drwx------ 3 postgres postgres 58 Aug 17 19:31 pg_wal
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_subtrans
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_notify
drwx------ 2 postgres postgres 4096 Aug 17 19:31 global
drwx------ 5 postgres postgres 38 Aug 17 19:31 base
drwx------ 4 postgres postgres 65 Aug 17 19:31 pg_logical
[postgres@example03 pgsql]$
start and stop services:
--------------------------------
To stop and start postgresql services we have an utility called pg_ctl.
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: no server running
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:33:36.642 PDT [14521] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:33:36.642 PDT [14521] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:33:36.645 PDT [14521] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:33:36.655 PDT [14522] LOG: database system was shut down at 2019-08-17 19:31:15 PDT
2019-08-17 19:33:36.657 PDT [14521] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: server is running (PID: 14521)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[postgres@example03 pgsql]$ ps -ef|grep postgres
root 14457 2204 0 19:30 pts/0 00:00:00 su - postgres
postgres 14458 14457 0 19:30 pts/0 00:00:00 -bash
postgres 14521 1 0 19:33 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/pgsql
postgres 14523 14521 0 19:33 ? 00:00:00 postgres: checkpointer
postgres 14524 14521 0 19:33 ? 00:00:00 postgres: background writer
postgres 14525 14521 0 19:33 ? 00:00:00 postgres: walwriter
postgres 14526 14521 0 19:33 ? 00:00:00 postgres: autovacuum launcher
postgres 14527 14521 0 19:33 ? 00:00:00 postgres: stats collector
postgres 14528 14521 0 19:33 ? 00:00:00 postgres: logical replication launcher
postgres 14530 14458 0 19:33 pts/0 00:00:00 ps -ef
postgres 14531 14458 0 19:33 pts/0 00:00:00 grep --color=auto postgres
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop
waiting for server to shut down....2019-08-17 19:34:12.429 PDT [14521] LOG: received fast shutdown request
2019-08-17 19:34:12.433 PDT [14521] LOG: aborting any active transactions
2019-08-17 19:34:12.436 PDT [14521] LOG: background worker "logical replication launcher" (PID 14528) exited with exit code 1
2019-08-17 19:34:12.436 PDT [14523] LOG: shutting down
2019-08-17 19:34:12.446 PDT [14521] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$ ps -ef|grep postgres
root 14457 2204 0 19:30 pts/0 00:00:00 su - postgres
postgres 14458 14457 0 19:30 pts/0 00:00:00 -bash
postgres 14533 14458 0 19:34 pts/0 00:00:00 ps -ef
postgres 14534 14458 0 19:34 pts/0 00:00:00 grep --color=auto postgres
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:34:21.739 PDT [14537] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:21.739 PDT [14537] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:21.742 PDT [14537] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:21.754 PDT [14538] LOG: database system was shut down at 2019-08-17 19:34:12 PDT
2019-08-17 19:34:21.757 PDT [14537] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ reload
server signaled
2019-08-17 19:34:29.474 PDT [14537] LOG: received SIGHUP, reloading configuration files
[postgres@example03 pgsql]$
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ restart
waiting for server to shut down....2019-08-17 19:34:58.743 PDT [14537] LOG: received fast shutdown request
2019-08-17 19:34:58.745 PDT [14537] LOG: aborting any active transactions
2019-08-17 19:34:58.747 PDT [14537] LOG: background worker "logical replication launcher" (PID 14544) exited with exit code 1
2019-08-17 19:34:58.747 PDT [14539] LOG: shutting down
2019-08-17 19:34:58.756 PDT [14537] LOG: database system is shut down
done
server stopped
waiting for server to start....2019-08-17 19:34:58.851 PDT [14547] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:58.851 PDT [14547] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:58.854 PDT [14547] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:58.865 PDT [14548] LOG: database system was shut down at 2019-08-17 19:34:58 PDT
2019-08-17 19:34:58.869 PDT [14547] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$
Note : default mode is fast shut down.
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m smart
waiting for server to shut down....2019-08-17 19:37:31.428 PDT [14547] LOG: received smart shutdown request
2019-08-17 19:37:31.432 PDT [14547] LOG: background worker "logical replication launcher" (PID 14554) exited with exit code 1
2019-08-17 19:37:31.432 PDT [14549] LOG: shutting down
2019-08-17 19:37:31.441 PDT [14547] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:37:45.214 PDT [14562] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:37:45.214 PDT [14562] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:37:45.216 PDT [14562] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:37:45.226 PDT [14563] LOG: database system was shut down at 2019-08-17 19:37:31 PDT
2019-08-17 19:37:45.229 PDT [14562] LOG: database system is ready to accept connections
done
server started
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m immediate
waiting for server to shut down....2019-08-17 19:37:55.603 PDT [14562] LOG: received immediate shutdown request
2019-08-17 19:37:55.605 PDT [14567] WARNING: terminating connection because of crash of another server process
2019-08-17 19:37:55.605 PDT [14567] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-08-17 19:37:55.605 PDT [14567] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2019-08-17 19:37:55.607 PDT [14562] LOG: database system is shut down
done
server stopped
[postgres@example03 pgsql]$
how to enable services auto start:
----------------------------------------------
[root@example03 lib]# cd /var/lib/pgsql/postgresql-11.0/contrib/start-scripts/
[root@example03 start-scripts]# cp linux /etc/init.d/postgresql10
[root@example03 start-scripts]# chmod 775 /etc/init.d/postgresql10
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14573)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 stop
Stopping PostgreSQL: 2019-08-17 19:43:12.244 PDT [14573] LOG: received fast shutdown request
2019-08-17 19:43:12.249 PDT [14573] LOG: aborting any active transactions
2019-08-17 19:43:12.253 PDT [14573] LOG: background worker "logical replication launcher" (PID 14580) exited with exit code 1
2019-08-17 19:43:12.253 PDT [14575] LOG: shutting down
2019-08-17 19:43:12.267 PDT [14573] LOG: database system is shut down
ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: no server running
[root@example03 start-scripts]# /etc/init.d/postgresql10 start
Starting PostgreSQL: ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14845)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#
in the file fill proper installation location and data directory location.
[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 60
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_tblspc:all the user created tablespaces information will be recorded here.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_snapshots: if you create any snapshots, that information will be recorded here, just awr report.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_replslot: during the replication setup we will disucss
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_dynshmem: shared memory dynamic allocation information will be recorded here.
drwx------ 2 postgres postgres 6 Aug 17 19:31 pg_commit_ts: all the commited transaction infromation will be recorded here
-rw------- 1 postgres postgres 3 Aug 17 19:31 PG_VERSION: it will displays the version
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf --complete parameter managed by this file
-rw------- 1 postgres postgres 88 Aug 17 19:31 postgresql.auto.conf: if you are setting parameter by using alter system set command, those entries will be recorded here.
-rw------- 1 postgres postgres 1636 Aug 17 19:31 pg_ident.conf: some times people will try to conenct db with ldap account, in that case that user details we have to mention here.
-rw------- 1 postgres postgres 4513 Aug 17 19:31 pg_hba.conf:to prevent/manage remote connections
drwx------ 2 postgres postgres 17 Aug 17 19:3pg_Wal : all the wal files will be recorded here.
drwx------ 2 postgres postgres 17 Aug 17 19:31 pg_subtrans: as part of joins, temp tables creations more subtransactions will happens, that data will be managed here.
drwx------ 2 postgres postgres 4096 Aug 17 19:31 global:only global obejcts infromation will be stored here (users, user access rigths, tablesapces)
drwx------ 5 postgres postgres 38 Aug 17 19:31 base: our objects actual data (tables,materilized views,database)
drwx------ 4 postgres postgres 65 Aug 17 19:43 pg_logical: as part of logical replication data will be stored here.
-rw------- 1 postgres postgres 48 Aug 17 19:43 postmaster.opts
[postgres@example03 pgsql]$ cat postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
drwx------ 2 postgres postgres 17 Aug 17 19:43 pg_notify: during server start up some times we will get error messages with hint, that notification data will be stored here.
-rw-rw-r-- 1 postgres postgres 705 Aug 17 19:43 serverlog : all the activites will be recorded here.
[postgres@example03 pgsql]$ cat serverlog
2019-08-17 19:43:19.082 PDT [14845] LOG: listening on IPv6 address "::1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:43:19.082 PDT [14845] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:43:19.084 PDT [14845] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:43:19.099 PDT [14846] LOG: database system was shut down at 2019-08-17 19:43:12 PDT
2019-08-17 19:43:19.109 PDT [14845] LOG: database system is ready to accept connections
[postgres@example03 pgsql]$
-rw------- 1 postgres postgres 77 Aug 17 19:43 postmaster.pid
[postgres@example03 pgsql]$ cat postmaster.pid
14845
/usr/pgsql
1566096199
5432
/tmp
localhost
5432001 294912
ready
[postgres@example03 pgsql]$ ps -ef|grep 14845
postgres 14845 1 0 19:43 ? 00:00:00 /usr/local/pgsql/bin/postmaster -D /usr/pgsql
postgres 14847 14845 0 19:43 ? 00:00:00 postgres: checkpointer
postgres 14848 14845 0 19:43 ? 00:00:00 postgres: background writer
postgres 14849 14845 0 19:43 ? 00:00:00 postgres: walwriter
postgres 14850 14845 0 19:43 ? 00:00:00 postgres: autovacuum launcher
postgres 14851 14845 0 19:43 ? 00:00:00 postgres: stats collector
postgres 14852 14845 0 19:43 ? 00:00:00 postgres: logical replication launcher
postgres 14957 14875 0 19:59 pts/0 00:00:00 grep --color=auto 14845
[postgres@example03 pgsql]$
drwx------ 2 postgres postgres 6 Aug 17 19:43 pg_stat: all the statistics will be stored here.
drwx------ 2 postgres postgres 24 Aug 17 19:44 pg_stat_tmp:temp statistics, for which database how much size of temp files created, how many temp files created.
Click here to proceed connect database with user and manager user in database
