SRI SRI's Postgresql Knowledge

  Recently I found huge archive logs generation on one of the servers(for my client). Around 3500 files per day and it needs 3500 * 16 MB = 54GB space every day and space is low on the server. First thing came to my mind was checkpoint parameters, I had a look at them and they were at default. Hmm, so had changed them as below: checkpoint_segments = 180 checkpoint_timeout = 30min checkpoint_completion_target = 0.9  Monitored for couple of days and found that archive logs generation decreased to 50%(around 1700 files).  With the above parameter settings, archive log generation should be minimal. However if not, Here are some general suggestions to look at: -- Make sure that you don't UPDATE a row to the same values it already has. The row will be logged even if old and new values match. One way to handle this is to make sure that any UPDATE statements which might set a column to its existing value include in the WHERE clause a test that oldvalue IS DISTINCT FROM new value. Or you ca

  In this blog post, we dive into the nuts and bolts of setting up Streaming Replication (SR) in PostgreSQL. Streaming replication is the fundamental building block for achieving high availability in your  PostgreSQL hosting , and is produced by running a master-slave configuration. Master-Slave Terminology Master/Primary Server The server that can take writes. Also called read/write server. Slave/Standby Server A server where the data is kept in sync with the master continuously. Also called backup server or replica. A warm standby server is one that cannot be connected to until it is promoted to become a master server. In contrast, a hot standby server can accept connections and serves read-only queries. For the rest of this discussion, we will be focusing only on hot standby servers. Data is written to the master server and propagated to the slave servers. In case there are an issue with the existing master server, one of the slave servers will take over and continue to take writes

  pg_ctl -- initialize, start, stop, or control a PostgreSQL server pg_ctl [ status | start | stop | restart | reload | init ] [-U username] [-P password] [--help] pg_ctl --help pg_ctl --version pg_ctl init[db] [-s] [-D datadir] [-o initdb-options] pg_ctl -D /usr/local/pgsql/data initdb pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c] pg_ctl start pg_ctl -w start pg_ctl -D /data/postgres/9.6/5304 start pg_ctl start -l /data/logs pg_ctl -o "-F -p 5433" start pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] pg_ctl stop pg_ctl stop -D /Library/PostgreSQL/9.6/data pg_ctl --pgdata /usr/local/var/postgres stop pg_ctl stop -m fast pg_ctl stop -m smart pg_ctl stop -m immediate pg_ctl stop -w -l /home/pg/logs pg_ctl -D /usr/local/pgsql/data stop -m fast pg_ctl status [-D datadir] pg_ctl status pg_ctl -D /data/postgres/9.6/5301 status pg_ctl -D /opt/data/pg9-9.5.0/data/5361 status  pg_ctl reload [-s] [-D d