Come back with my
tutorial DBA, In here i want to tell you how to see log file
postgresql and how can do archive file log to tar if client want to
see log on postgres. ok follow my step:
The one, you must be
login on your pc have installed postgresql, if you have not postgres,
you can to see my tutorial install postgresql, ok. back to login, so
in here i used remote pc with ssh on centos 7, with user "root"
and input your "password".
dba@dba-rizky:~$ ssh
root@x.x.x.x
root@x.x.x.x's
password:
Last login: Thu Apr
19 16:54:32 2018 from dba
After finished
login, you can check location with command “pwd”
:
[root@prod-odgpostgres
~]#pwd
/root
Next, you can see
status network postgres, whats off or on in default port? if not show
to the port postgres, you can start postgres before show log and
archive file to tar with follow command "pg_ctl" in binary
and then call location data postgres. in here you can check status
network whats off or on with command "netstat -pltn":
[root@prod-odgpostgres
~]# netstat -pltn
Active Internet
connections (only servers)
Proto Recv-Q Send-Q
Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111
0.0.0.0:* LISTEN 1/systemd
tcp 0 0 0.0.0.0:22
0.0.0.0:* LISTEN 1101/sshd
tcp6 0 0 :::111 :::*
LISTEN 1/systemd
tcp6 0 0 :::22 :::*
LISTEN 1101/sshd
And after you see
status but not show port 5432 you can do start postgres with follow
command "pg_ctl" in binary and then call location data
postgres:
[root@prod-odgpostgres
]#/usr/local/pgsql/bin/pg_ctl start -D
/usr/local/pgsql/data
And after start you
can back to command "netstat -pltn", and you can see port
5432 already.
[root@prod-odgpostgres
~]# netstat -pltn
Active Internet
connections (only servers)
Proto Recv-Q Send-Q
Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111
0.0.0.0:* LISTEN 1/systemd
tcp 0 0 0.0.0.0:22
0.0.0.0:* LISTEN 1101/sshd
tcp 0 0 0.0.0.0:5432
0.0.0.0:* LISTEN 13070/postgres
tcp6 0 0 :::111 :::*
LISTEN 1/systemd
tcp6 0 0 :::22 :::*
LISTEN 1101/sshd
tcp6 0 0 :::5432
:::* LISTEN 13070/postgres
And then, the next
steps you can the move location postgres in the data and dont forget
move user "root" to user "postgres", in there you
can see the log file "serverlog" and you can see to the
content inside with follow command :
[root@prod-odgpostgres
~]# su - postgres
[postgres@prod-odgpostgres
~]$ ps -ef | grep postgres
postgres 22114 1 0
10:57 ? 00:00:00 /usr/local/pgsql/bin/postgres
-D /usr/local/pgsql/data
[postgres@prod-odgpostgres
~]$ cd /usr/local/pgsql/data
[postgres@prod-odgpostgres
data]$ ll
total 12160
drwxrwxr-x 2
postgres postgres 4096 Apr 24 10:58 archive
drwx------ 6
postgres postgres 4096 Mar 13 11:32 base
drwx------ 2
postgres postgres 4096 Apr 24 14:10 global
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_clog
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_commit_ts
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_dynshmem
-rw------- 1
postgres postgres 4687 Apr 23 15:12 pg_hba.conf
-rw------- 1
postgres postgres 1636 Oct 3 2017 pg_ident.conf
drwx------ 4
postgres postgres 4096 Oct 3 2017 pg_logical
drwx------ 4
postgres postgres 4096 Oct 3 2017 pg_multixact
drwx------ 2
postgres postgres 4096 Apr 24 10:57 pg_notify
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_replslot
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_serial
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_snapshots
drwx------ 2
postgres postgres 4096 Apr 24 10:57 pg_stat
drwx------ 2
postgres postgres 4096 Apr 24 16:21 pg_stat_tmp
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_subtrans
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_tblspc
drwx------ 2
postgres postgres 4096 Oct 3 2017 pg_twophase
-rw------- 1
postgres postgres 4 Oct 3 2017 PG_VERSION
drwx------ 3
postgres postgres 4096 Apr 24 10:58 pg_xlog
-rw------- 1
postgres postgres 88 Oct 3 2017 postgresql.auto.conf
-rw------- 1
postgres postgres 22657 Apr 24 10:57 postgresql.conf
-rw------- 1
postgres postgres 59 Apr 24 10:57 postmaster.opts
-rw------- 1
postgres postgres 71 Apr 24 10:57 postmaster.pid
-rw-r--r-- 1 root
root 12317008 Apr 24 10:55 serverlog
[postgres@prod-odgpostgres
data]$ tail -300f serverlog
And in here you can
see the last log process postgresql in your pc (you can see log query
running in here and all processed query log in here)
LOG:
database system was shut down at 2018-04-23 14:12:18 WIB
LOG:
MultiXact member wraparound protections are now enabled
LOG:
database system is ready to accept connections
LOG:
autovacuum launcher started
LOG:
received fast shutdown request
LOG:
aborting any active transactions
LOG:
autovacuum launcher shutting down
LOG:
shutting down
LOG:
database system is shut down
LOG:
database system was shut down at 2018-04-23 14:14:53 WIB
LOG:
MultiXact member wraparound protections are now enabled
LOG:
database system is ready to accept connections
LOG:
autovacuum launcher started
LOG:
received fast shutdown request
LOG:
aborting any active transactions
LOG:
autovacuum launcher shutting down
LOG:
shutting down
LOG:
database system is shut down
Ok you can see log
file finished, but if your client want to take a data log postgres,
you can see with building “tar” because your file will be to
compress if your size file is large. follow command:
example :
[postgres@prod-odgpostgres
data]$tar -czvf serverlog.tar.gz
/home/postgres/
And give your pc
client with command "scp"
[postgres@prod-odgpostgres
data]$scp serverlog.tar.gz
root@ipclients:/root/
If you want file
“zip” you can follow command:
[postgres@prod-odgpostgres
data]$zip serverlog.zip
/usr/local/pgsql/data/serverlog
[postgres@prod-odgpostgres
data]$scp serverlog.zip root@ipclients:/root/
Comments
Post a Comment