TutorialDBA - Support | Training | Consultant

1.Install the new version of postgresql software and stop the new server. 2.Stop the old version of postgresql server. 3.start the new version of postgresql server using the old version data path. 4.Use slony replication.

Question : We are planning to change deadlock_timeout from 1second to 180 seconds. Will it impact the system? If yes, what are they. Answer : Getting little bit slow ur server because this lock already taken resources so it will not release resources until 180 seconds

Questions :I have three  tables which contains around 20millions of records. I am planning to update these three tables for all records. Could you suggest me best approaches to meet this, please? Ans : 1.I think disable index before update ? And autovacuum off,arhvive off if possible. 2.Increase the check point time out Increase shared buffer or wall beffer if archive enable. 3.Check any parallel option is there.

If somebody removed postmaster.pid file Server will be going to down becouse postgres server running based on postmaster.pid file so you need to start the server manually  after started the postgres server posmaster.pid file will be created automatically [root@p1 data]# pwd /opt/PostgreSQL/9.3/data[ root@p1 data]# mv postmaster.pid /home/ mv: overwrite `/home/postmaster.pid'? y bash-3.2$ pg_ctl -D /opt/PostgreSQL/9.3/data/ status pg_ctl: no server running after started the postgres server postmaster process id will be varried see below example first one is old postmaster.pid file next is new postmaster.pid file Old Postmaster.pid file: [root@p1 home]# cat postmaster.pid 13750 /opt/PostgreSQL/9.3/data 1494571616 5432 /tmp * 5432001 3932161 New Postmaster.pid file After started postgres server: [root@p1 data]# cat postmaster.pid 13957 /opt/PostgreSQL/9.3/data 1494572359 5432 /tmp * 5432001 3964929

Check the postmaster process id before killing it the process id is 9590   [root@p1 data]# ps -ef |grep post postgres 9590 1 0 10:54 pts/1 00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data postgres 9593 9590 0 10:54 ? 00:00:00 postgres: checkpointer process postgres 9594 9590 0 10:54 ? 00:00:00 postgres: writer process postgres 9595 9590 0 10:54 ? 00:00:00 postgres: wal writer process postgres 9596 9590 0 10:54 ? 00:00:00 postgres: autovacuum launcher process postgres 9597 9590 0 10:54 ? 00:00:00 postgres: archiver process postgres 9598 9590 0 10:54 ? 00:00:00 postgres: stats collector process root 9668 25224 0 May07 pts/1 00:00:00 su postgres postgres 9669 9668 0 May07 pts/1 00:00:00 bash postgres ...

--Check the Process id of syslogger whether it is running or not. [root@p1 data]# pwd /opt/PostgreSQL/9.3/data [root@p1 data]# ls -lrt postmaster* -rw------- 1 postgres postgres 73 Apr 26 15:10 postmaster.pid -rw------- 1 postgres postgres 65 Apr 26 15:10 postmaster.opts [root@p1 data]# cat postmaster.pid 9590 /opt/PostgreSQL/9.3/data 1493233830 5432 /tmp * 5432001 3801089 [root@p1 data]# ps -ef |grep post postgres 9590 1 0 10:54 pts/1 00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data postgres 9591 9590 0 10:54 ? 00:00:00 postgres: logger process postgres 9593 9590 0 10:54 ? 00:00:00 postgres: checkpointer process postgres 9594 9590 0 10:54 ? 00:00:00 postgres: writer process postgres 9595 9590 0 10:54 ? 00:00:00 postgres: wal writer process postgres 9596 9590 0 10:54 ? ...

We can tune query is that Query whether ping or not in s hared_buffers Shared_buffers The shared_buffers is simply an array of 8KB blocks.Each page has metadata within itself to distinguish itself as mentioned above. Before postgres checks out the data from the disk, it first does a lookup for the pages in the shared_buffers, if there is a hit then it returns the data from there itself and thereby avoiding a disk I/O.  This Query can even tell how much data blocks came from disk and how much came from shared_buffers i.e memory. A query plan below gives an example, performance_test=# explain (analyze,buffers) select * from users order by userid limit 10; Limit (cost=0.42..1.93 rows=10 width=219) (actual time=32.099..81.529 rows=10 loops=1) Buffers: shared read=13 -> Index Scan using users_userid_idx on users (cost=0.42..150979.46 rows=1000000 width=219) (actual time=32.096..81.513 rows=10 loops=1) Buffers: shared read=13 Planning time: 0...