How to create instance in PostgreSQL - Query

 

How to create instance in PostgreSQL

-bash-4.2$ pwd

/u02/PostgreSQL/11/data

-bash-4.2$

-bash-4.2$ cd ..

-bash-4.2$ ls -lrt

total 6956

drwx------  3 postgres postgres    4096 Mar 30  2020 rollbackBackupDirectory

drwx------  3 postgres postgres    4096 Mar 30  2020 OmniDB

drwx------  4 postgres postgres    4096 Mar 30  2020 pl-languages

-rwx------  1 postgres postgres 6946528 Mar 30  2020 Uninstall11

-rwx------  1 postgres postgres  138094 Mar 30  2020 Uninstall11.dat

drwx------  6 postgres postgres    4096 Mar 30  2020 11

-rw-------  1 postgres postgres     358 Mar 30  2020 pg_upgrade_utility.log

-rw-------  1 postgres postgres    1315 Mar 30  2020 pg_upgrade_internal.log

-rw-------  1 postgres postgres    3459 Mar 30  2020 pg_upgrade_server.log

drwx------ 19 postgres postgres    4096 Oct 23 14:33 data

drwx------  3 postgres postgres    4096 Oct 23 21:11 base2

-bash-4.2$

-bash-4.2$ mkdir pgdata

-bash-4.2$

-bash-4.2$

-bash-4.2$ ls -lrt

total 6960

drwx------  3 postgres postgres    4096 Mar 30  2020 rollbackBackupDirectory

drwx------  3 postgres postgres    4096 Mar 30  2020 OmniDB

drwx------  4 postgres postgres    4096 Mar 30  2020 pl-languages

-rwx------  1 postgres postgres 6946528 Mar 30  2020 Uninstall11

-rwx------  1 postgres postgres  138094 Mar 30  2020 Uninstall11.dat

drwx------  6 postgres postgres    4096 Mar 30  2020 11

-rw-------  1 postgres postgres     358 Mar 30  2020 pg_upgrade_utility.log

-rw-------  1 postgres postgres    1315 Mar 30  2020 pg_upgrade_internal.log

-rw-------  1 postgres postgres    3459 Mar 30  2020 pg_upgrade_server.log

drwx------ 19 postgres postgres    4096 Oct 23 14:33 data

drwx------  3 postgres postgres    4096 Oct 23 21:11 base2

drwxrwxr-x  2 postgres postgres    4096 Oct 24 00:00 pgdata

-bash-4.2$ cd pgdata/

-bash-4.2$ ls -lrt

total 0

-bash-4.2$

-bash-4.2$ initdb -D pgdata

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.

creating directory pgdata ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default timezone ... Asia/Kolkata

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:

    pg_ctl -D pgdata -l logfile start

-bash-4.2$ ls -lrt

total 4

drwx------ 19 postgres postgres 4096 Oct 24 00:00 pgdata

-bash-4.2$

-bash-4.2$ pwd

/u02/PostgreSQL/11/pgdata

-bash-4.2$ cd pgdata/

-bash-4.2$ ls -lrt

total 112

-rw------- 1 postgres postgres     3 Oct 24 00:00 PG_VERSION

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_twophase

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_tblspc

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_stat_tmp

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_stat

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_snapshots

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_serial

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_replslot

drwx------ 4 postgres postgres  4096 Oct 24 00:00 pg_multixact

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_dynshmem

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_commit_ts

-rw------- 1 postgres postgres 22873 Oct 24 00:00 postgresql.conf

-rw------- 1 postgres postgres    88 Oct 24 00:00 postgresql.auto.conf

-rw------- 1 postgres postgres  1636 Oct 24 00:00 pg_ident.conf

-rw------- 1 postgres postgres  4513 Oct 24 00:00 pg_hba.conf

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_xact

drwx------ 3 postgres postgres  4096 Oct 24 00:00 pg_wal

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_subtrans

drwx------ 2 postgres postgres  4096 Oct 24 00:00 pg_notify

drwx------ 2 postgres postgres  4096 Oct 24 00:00 global

drwx------ 5 postgres postgres  4096 Oct 24 00:00 base

drwx------ 4 postgres postgres  4096 Oct 24 00:00 pg_logical

-bash-4.2$

-bash-4.2$ pg_ctl start -D /u02/PostgreSQL/11/pgdata/pgdata

waiting for server to start....2021-10-24 00:10:18.711 IST [47827] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use

2021-10-24 00:10:18.711 IST [47827] HINT:  Is another postmaster already running on port 5433? If not, wait a few seconds and retry.

2021-10-24 00:10:18.711 IST [47827] WARNING:  could not create listen socket for "localhost"

2021-10-24 00:10:18.711 IST [47827] FATAL:  could not create any TCP/IP sockets

2021-10-24 00:10:18.711 IST [47827] LOG:  database system is shut down

 stopped waiting

pg_ctl: could not start server

Examine the log output.

-bash-4.2$

-bash-4.2$

vi postgresq.conf

port=5001

log_destination = 'stderr'      

logging_collector = on      

log_rotation_age = 0            

client_min_messages = notice

log_min_messages = warning

log_min_error_statement = error

log_min_duration_statement = 0

log_checkpoints = on

log_connections = on

log_disconnections = on

log_duration = on

log_error_verbosity = verbose       

log_hostname = on

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '          

log_lock_waits = on         

log_statement = 'all'       

log_temp_files = 0      

:wq

# Add settings for extensions here

-bash-4.2$

-bash-4.2$ pg_ctl start -D /u02/PostgreSQL/11/pgdata/pgdata

waiting for server to start....2021-10-24 00:14:11 IST [48085]: [1-1] user=,db= LOG:  00000: listening on IPv4 address "127.0.0.1", port 5001

2021-10-24 00:14:11 IST [48085]: [2-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:593

2021-10-24 00:14:11 IST [48085]: [3-1] user=,db= LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.5001"

2021-10-24 00:14:11 IST [48085]: [4-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:587

2021-10-24 00:14:11 IST [48085]: [5-1] user=,db= LOG:  00000: redirecting log output to logging collector process

2021-10-24 00:14:11 IST [48085]: [6-1] user=,db= HINT:  Future log output will appear in directory "log".

2021-10-24 00:14:11 IST [48085]: [7-1] user=,db= LOCATION:  SysLogger_Start, syslogger.c:666

 done

server started

-bash-4.2$