Types of Replication general info


 Data is replicated after it has been commited. Data loss can exist
if the master server crushes before the changes are commited by the remote server.


System has to ensure the data written by the transaction will be present
to at least two servers when the transaction commits. Confirmation
from the remote server is needed and this creates an overhead.


Single Master Replication

The master server replicates the data to the slave server.
The writes go to the Master server and these changes are disturbuted
to the slaves servers.

Multi Master

In this server writes are allowed to all the servers in the cluster.
This means alot of writes can go to many nodes at the same time.


Logical Replication

This replication distributes the changes at a logical level. It
does not concern it self with physical layout of the data structure
of the database it self.

Physical Replication

Is the type of replication where data are moved as is. The replication
of data is done at a binary level.

Share Button

PostgreSQL 9.4 – Database dumps and Restores

Create a Compressed dump file of a Remote Database and Restore it

  • Create a highly compressed data dump
pg_dump -h -U seeker -C -c -v -F c -d servers -f data_dump.dump
  • Restore the data dump you created
pg_restore -C -v -U seeker -d servers -h  data_dump.dump

Create SQL data dumps and Restore them

  • Create a SQL Schema only dump
pg_dump -U  seeker  -d servers   -h --schema-only -f schema_only.sql
  • Create a SQL Data only dump
pg_dump -U  seeker  -d servers   -h --data-only -f data_only.sql
  • Restore SQL data dump using psql
\i "file_name".sql
     Important Notes about Backing up and Restoring

The database, schema and the role must be first created.

Back up and Restore specific tables

  • Backup a specific table
 pg_dump -Fc -v -U seeker -d servers -t a_table > /tmp/a_table.pgdump
  • View the tables of a data dump
pg_restore -l  data_dump_file.dump
  • Extract a table from a dump file
pg_restore -U seeker --data-only --table=a_table database.pgdump > a_table.pg
  • Upload the data of this table to a table in the database
 psql -U user_name -d database_name < name_of_your_dump.pg
Share Button

pg_dump incompatibility issue between servers

You may have to do pg_dump from server that has pg_dump version 9.4 to remote server that has pg_dump version 9.3. An error like this may occur.

 pg_dump: server version: 9.4.4; pg_dump version: 9.3.10 

You will have to install the 9.4 version of postgres and then adjust the soft link /usr/bin/pg_dump to point to /usr/pgsql-9.4/bin/pg_dump

sudo ln -s /usr/pgsql-9.4/bin/pg_dump  /usr/bin/pg_dump
Share Button

Postgresql 9.4: Create a template database.

In postgres template1 is the default source database name for CREATE DATABASE. The template0 database is used after we specify it. More information at the official documentation located here.

The template0 database we create a user database that "contains none
of the site-local additions in template1. This is particularly handy
when restoring a pg_dump dump: the dump script should be restored
in a virgin database to ensure that one recreates the correct
contents of the dumped database. Another common reason for
copying template0 instead of template1 is that new encoding and
locale settings can be specified when copying template0, whereas a
copy of template1 must use the same settings it does. This is because
template1 might contain encoding-specific or locale-specific data,
while template0 is known not to."

You can create your own custom Template to be used in the creation of databases. For example, assume you have the database with the name ‘servers’. You can create a template out of that database with the following steps.

Make sure to kill all connections from the database and disallow any new connections.

update pg_database 
set datallowconn = false 
where datname = 'servers'

Kill any other live connections

select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'servers'

Create your template

create database servers_2 template servers;

Make sure to activate the template flag in your template database in order to avoid having the template being altered.

update servers_2 
set datistemplate = TRUE 
where datname = 'servers';

Remember to allow connections to the database you used to create the template.

update pg_database 
set datallowconn = true 
where datname = 'servers'
Share Button

Postgresql 9.4: System tables ‘pg_stat_activity’ and ‘pg_database’, Cancel/Kill/Stop connections queries to a database.

You can view the status of connections to the postgresql database and the status of the queries. This info can be sorted by different fields.

      Column      | Modifiers
 datid            |
 datname          | The database name
 pid              | The pid number
 usesysid         |
 usename          | The username
 application_name |
 client_addr      | The IP address connected
 client_hostname  |
 client_port      | The port used
 backend_start    | The time at which the database was accessed by the client
 xact_start       | The time at which the transaction started to run. Should always have occurred before the query_start time 
 query_start      | The time the actual query started 
 state_change     |
 waiting          |
 state            |
 backend_xid      |
 backend_xmin     |
 query            | The actual query

View queries for specific user

datname, pid, usename, client_addr, query_start, query
from pg_stat_activity where usename = 'seeker';
datname     | servers
pid         | 21694
usename     | seeker
client_addr |
query_start | 2015-10-16 15:59:08.234508-04
query       | INSERT INTO IP_DNS_SCAN(hostname, ipaddress, remote_ip, pingStatus, hostDNScheck) VALUES ('superman.sfentona.lol','10.0.

You can cancel or Kill queries by using pg_terminate_backend or pg_cancel_backend

Cancel or Kill the queries of a specific pid

select pg_terminate_backend(pid) 
from pg_stat_activity 
where pid = 24297;

Cancel or Kill the queries of a specific user

select pg_terminate_backend(pid)
from pg_stat_activity 
where usename = 'gmastrokostas';

Kill all queries besides your own

select pg_terminate_backend(pid)
from pg_stat_activity
where usename <> 'gmastrokostas';

Disallow all new connections to a specific Database

update pg_database 
set datallowconn = FALSE 
wnere datname = 'name_your_database';
Share Button

PostgreSQL 9.4: System Table – ‘pg_settings” – Queries that display Postgresql configuration settings

Obtaining Configuration settings.

Get Version of Database

select version();

Show Data directory

show data_directory;

Show config files

select name, setting 
from pg_settings
where category = 'File Locations';


Query your postgresql.conf file via pg_settings

name      - Name of Setting
context   - Does it require a hard or soft restart (postgres yes - user no)
unit      - MBs, KBs etc
setting   - Current value
boot_val  - Default setting
reset_val - New value once posgres service is restarted


name, context, unit, setting, boot_val, reset_val
from pg_settings
where name in 
('listen_addresses', 'max_connections', 'shared_buffers');
       name       |  context   | unit | setting | boot_val  | reset_val
 listen_addresses | postmaster |      | *       | localhost | *
 max_connections  | postmaster |      | 100     | 100       | 100
 shared_buffers   | postmaster | 8kB  | 16384   | 1024      | 16384
(3 rows)

All pg_settings fields you can query
All pg_settings fields

name | allow_system_table_mods
name | application_name
name | archive_command
name | archive_mode
name | archive_timeout
name | array_nulls
name | authentication_timeout
name | autovacuum
name | autovacuum_analyze_scale_factor
name | autovacuum_analyze_threshold
name | autovacuum_freeze_max_age
name | autovacuum_max_workers
name | autovacuum_multixact_freeze_max_age
name | autovacuum_naptime
name | autovacuum_vacuum_cost_delay
name | autovacuum_vacuum_cost_limit
name | autovacuum_vacuum_scale_factor
name | autovacuum_vacuum_threshold
name | autovacuum_work_mem
name | backslash_quote
name | bgwriter_delay
name | bgwriter_lru_maxpages
name | bgwriter_lru_multiplier
name | block_size
name | bonjour
name | bonjour_name
name | bytea_output
name | check_function_bodies
name | checkpoint_completion_target
name | checkpoint_segments
name | checkpoint_timeout
name | checkpoint_warning
name | client_encoding
name | client_min_messages
name | commit_delay
name | commit_siblings
name | config_file
name | constraint_exclusion
name | cpu_index_tuple_cost
name | cpu_operator_cost
name | cpu_tuple_cost
name | cursor_tuple_fraction
name | data_checksums
name | data_directory
name | DateStyle
name | db_user_namespace
name | deadlock_timeout
name | debug_assertions
name | debug_pretty_print
name | debug_print_parse
name | debug_print_plan
name | debug_print_rewritten
name | default_statistics_target
name | default_tablespace
name | default_text_search_config
name | default_transaction_deferrable
name | default_transaction_isolation
name | default_transaction_read_only
name | default_with_oids
name | dynamic_library_path
name | dynamic_shared_memory_type
name | effective_cache_size
name | effective_io_concurrency
name | enable_bitmapscan
name | enable_hashagg
name | enable_hashjoin
name | enable_indexonlyscan
name | enable_indexscan
name | enable_material
name | enable_mergejoin
name | enable_nestloop
name | enable_seqscan
name | enable_sort
name | enable_tidscan
name | escape_string_warning
name | event_source
name | exit_on_error
name | external_pid_file
name | extra_float_digits
name | from_collapse_limit
name | fsync
name | full_page_writes
name | geqo
name | geqo_effort
name | geqo_generations
name | geqo_pool_size
name | geqo_seed
name | geqo_selection_bias
name | geqo_threshold
name | gin_fuzzy_search_limit
name | hba_file
name | hot_standby
name | hot_standby_feedback
name | huge_pages
name | ident_file
name | ignore_checksum_failure
name | ignore_system_indexes
name | integer_datetimes
name | IntervalStyle
name | join_collapse_limit
name | krb_caseins_users
name | krb_server_keyfile
name | lc_collate
name | lc_ctype
name | lc_messages
name | lc_monetary
name | lc_numeric
name | lc_time
name | listen_addresses
name | lo_compat_privileges
name | local_preload_libraries
name | lock_timeout
name | log_autovacuum_min_duration
name | log_checkpoints
name | log_connections
name | log_destination
name | log_directory
name | log_disconnections
name | log_duration
name | log_error_verbosity
name | log_executor_stats
name | log_file_mode
name | log_filename
name | log_hostname
name | log_line_prefix
name | log_lock_waits
name | log_min_duration_statement
name | log_min_error_statement
name | log_min_messages
name | log_parser_stats
name | log_planner_stats
name | log_rotation_age
name | log_rotation_size
name | log_statement
name | log_statement_stats
name | log_temp_files
name | log_timezone
name | log_truncate_on_rotation
name | logging_collector
name | maintenance_work_mem
name | max_connections
name | max_files_per_process
name | max_function_args
name | max_identifier_length
name | max_index_keys
name | max_locks_per_transaction
name | max_pred_locks_per_transaction
name | max_prepared_transactions
name | max_replication_slots
name | max_stack_depth
name | max_standby_archive_delay
name | max_standby_streaming_delay
name | max_wal_senders
name | max_worker_processes
name | password_encryption
name | port
name | post_auth_delay
name | pre_auth_delay
name | quote_all_identifiers
name | random_page_cost
name | restart_after_crash
name | search_path
name | segment_size
name | seq_page_cost
name | server_encoding
name | server_version
name | server_version_num
name | session_preload_libraries
name | session_replication_role
name | shared_buffers
name | shared_preload_libraries
name | sql_inheritance
name | ssl
name | ssl_ca_file
name | ssl_cert_file
name | ssl_ciphers
name | ssl_crl_file
name | ssl_ecdh_curve
name | ssl_key_file
name | ssl_prefer_server_ciphers
name | ssl_renegotiation_limit
name | standard_conforming_strings
name | statement_timeout
name | stats_temp_directory
name | superuser_reserved_connections
name | synchronize_seqscans
name | synchronous_commit
name | synchronous_standby_names
name | syslog_facility
name | syslog_ident
name | tcp_keepalives_count
name | tcp_keepalives_idle
name | tcp_keepalives_interval
name | temp_buffers
name | temp_file_limit
name | temp_tablespaces
name | TimeZone
name | timezone_abbreviations
name | trace_notify
name | trace_recovery_messages
name | trace_sort
name | track_activities
name | track_activity_query_size
name | track_counts
name | track_functions
name | track_io_timing
name | transaction_deferrable
name | transaction_isolation
name | transaction_read_only
name | transform_null_equals
name | unix_socket_directories
name | unix_socket_group
name | unix_socket_permissions
name | update_process_title
name | vacuum_cost_delay
name | vacuum_cost_limit
name | vacuum_cost_page_dirty
name | vacuum_cost_page_hit
name | vacuum_cost_page_miss
name | vacuum_defer_cleanup_age
name | vacuum_freeze_min_age
name | vacuum_freeze_table_age
name | vacuum_multixact_freeze_min_age
name | vacuum_multixact_freeze_table_age
name | wal_block_size
name | wal_buffers
name | wal_keep_segments
name | wal_level
name | wal_log_hints
name | wal_receiver_status_interval
name | wal_receiver_timeout
name | wal_segment_size
name | wal_sender_timeout
name | wal_sync_method
name | wal_writer_delay
name | work_mem
name | xmlbinary
name | xmloption
name | zero_damaged_pages

Share Button

PostgreSQL 9.4 – System Tables – Get Database/Table number, size and other general info for your database

Get information about your existing databases

select * from pg_database;

Show uptime of Postgres server (Not uptime of OS)

select date_trunc ('second', current_timestamp - pg_postmaster_start_time());

View how much space a database consumes

select pg_size_pretty(pg_database_size(current_database()));

View the largest tables in your database

select relname, relpages
from pg_class 
order by relpages desc;

view how large a table is

select pg_size_pretty(pg_relation_size('name_of_table'));


\dt+ name_of_table

view how many tables a user owns

select  count(*)  from pg_tables where tableowner = 'seeker';

View how many tables a schema of your database has.

select count(*) from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema');

View how many databases a server has.

select count(*)  from pg_database;

View size of all databases

select sum(pg_database_size(datname)) from pg_database;

List all roles

select * from information_schema.enabled_roles;

List all schemas

select schema_name from information_schema.schemata;
Share Button

Centos 7 – Part 5 – HAproxy systemctl script

vi /etc/systemd/system/haproxy

systemctl enable haproxy
Share Button

HAproxy – KeepAlived important notes.

  • Multicast must be enabled for fail over to work. Otherwise, If the haproxy service stops but the keepalived service is still running, the VirtualIP will still be pingable however any requests made to that IP will not be forwarded via HAproxy simply because HAproxy is no longer running. The keepalived will switch to the standby server only when the keepalived service it self stops from ServerA. Then ServerB will automatically take over.  In other words, failure of HAproxy does not mean Keepalive will automatically switch to the standby server.
  • If for whatever reason you wish to run a HA service with out SSL then your HAproxy config files should listen to the virtual IP address.
Share Button

Centos 7 – Part 4 – HAProxy Standby with SSL support combined with NGINX Load Balancing

These instructions expand on the previous post. The previous post shows how to implement HAPROXY with SSL in front of two NGINX load balancers with NGINX servers having Fail Over enabled.  This post will show how to create add another HAPROXY server in order to have fail over enabled,

As explained on the previous post, HAPROXY and keepalived needs to be installed.


Configure HAProxy.

The configuration file for server HAPROXY2 is the same as with the configuration file with server HAPROXY1, minus of course the IP address that we bind. Important: You must copy the ssl certificate files from HAPROXY1 to HAPROXY2 server under the directory specified in the config file.

        log   local0
        log   local1 debug
        maxconn   45000 # Total Max Connections.
        nbproc      1 # Number of processing cores.
        timeout server 86400000
        timeout connect 86400000
        timeout client 86400000
        timeout queue   1000s

frontend https_frontend
  bind ssl crt /etc/ssl/haproxy1.sfentona.lol/haproxy1.pem
  mode http
  option httpclose
  option forwardfor
  reqadd X-Forwarded-Proto:\ https
  default_backend web_server

backend web_server
  mode http
  balance roundrobin
  cookie SERVERID insert indirect nocache
  server wordpressvirtip

Configure Keepalived

Keep in mind that we already have keepalived running for the two NGINX load balancers. We have designated them in the keepalived.cfg as virtual_router_id 51. For the HAproxy servers we are going to assign them a different id. Servers HAPROXY1 and HAPROXY2 will now be designated as virtual_router_id 52 .

Keepalived config file for HAPROXY1

vrrp_script chk_haproxy {           # Requires keepalived-1.1.13
script "killall -0 haproxy"     # cheaper than pidof
interval 2                      # check every 2 seconds
weight 2                        # add 2 points of prio if OK
vrrp_instance VI_1 {
interface ens192
state MASTER
virtual_router_id 52
priority 101                    # 101 on master, 100 on backup
virtual_ipaddress {
track_script {

Keepalived config file for HAPROXY2

vrrp_script chk_haproxy {           # Requires keepalived-1.1.13
script "killall -0 haproxy"     # cheaper than pidof
interval 2                      # check every 2 seconds
weight 2                        # add 2 points of prio if OK
vrrp_instance VI_1 {
interface ens192
state MASTER
virtual_router_id 52
priority 100                    # 101 on master, 100 on backup
virtual_ipaddress {
track_script {

Configure your Firewall
The following IPTABLE rules should be running on both HAPROXY1 and HAPROXY2. Copy and paste the following rules in a text file and import them to your firewall table.

# Generated by iptables-save v1.4.21 on Thu Oct  8 15:18:59 2015
:INPUT ACCEPT [26988:2784395]
:OUTPUT ACCEPT [35111:2263400]
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p vrrp -j ACCEPT
# Completed on Thu Oct  8 15:18:59 2015
iptables-restore < /root/firewall.fw
Share Button