PostgreSQL 10 - pgpool II

#Install PostgreSQL

## Install from repository

### Install the repository RPM

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm
    
### Install the client packages:

yum install postgresql10
    
### Optionally install the server packages:

yum install postgresql10-server
    
### Optionally initialize the database and enable automatic start:

/usr/pgsql-10/bin/postgresql-10-setup initdb
    systemctl enable postgresql-10
    systemctl start postgresql-10
    
    su - postgres
    psql
    \password postgres
    xxxpgxxx
    
## Install from source

wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.gz
    tar zxvf postgresql-10.0.tar.gz
    cd postgresql-10.0
    
    export PYTHON=/usr/bin/python3
    ./configure --prefix=/usr/local/postgres10 --with-python --with-openssl  --with-libxml --with-ldap --with-libxslt --enable-thread-safety
    make
    make install
    
    mkdir /home/pgdata/pg10/pgdata
    ./initdb -D /home/pgdata/pg10/pgdata
        
    psql
    \password pgdata
    
    ./pg_ctl stop -D /var/lib/pgsql/10/data/ -m smart 
    ./pg_ctl start -D /var/lib/pgsql/10/data/ -l ../logs/pgdata.log 
    
##Install EXTENSION

### postgres_fdw

The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external PostgreSQL servers.

First install the extension

wget https://ftp.postgresql.org/pub/source/v9.5.1/postgresql-9.5.1.tar.gz
    tar zxvf postgresql-9.5.1.tar.gz
    ./configure --prefix=/pgdata/pgsql
    make
    cd contrib/postgres_fdw
    make
    make install
    
Here is an example of creating a foreign table with postgres_fdw

psql postgres
    
First create the extension

CREATE EXTENSION postgres_fdw;
    
Then create a foreign server using CREATE SERVER. In this example we wish to connect to a PostgreSQL server on host 192.83.123.89 listening on port 5432. The database to which the connection is made is named foreign_db on the remote server:

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host 'xxx-xxx-xxx', port '5432', dbname 'pgdata');
        
A user mapping, defined with CREATE USER MAPPING, is needed as well to identify the role that will be used on the remote server:

CREATE USER MAPPING FOR pgdata
        SERVER foreign_server
        OPTIONS (user 'pgdata', password 'xxx');
        
Now it is possible to create a foreign table with CREATE FOREIGN TABLE. In this example we wish to access the table named some_schema.some_table on the remote server. The local name for it will be foreign_table:

```
CREATE FOREIGN TABLE public.mj_company (
    id numeric NOT NULL,
	"name" varchar(256) NULL,
	type_id numeric NULL, 
	remark varchar(256) NULL,
	identity_code varchar NULL,
	laborsystem_insuranceid varchar(30) NULL,
	laborsystem_maintainerid varchar(32) NULL
)
SERVER foreign_server
OPTIONS (schema_name 'sso', table_name 'mj_company');
```

#Configure PostgreSQL

vi /var/lib/pgsql/10/data/postgresql.conf
    #modify
    listen_addresses = '*'
    wal_level = hot_standby
    max_wal_senders = 1
    hot_standby = on
    
    
    logging_collector =on                  
    log_line_prefix='%a %u %d %h %t %i %e '
    log_destination = 'csvlog'
    
    ---
    
    vi /var/lib/pgsql/10/data/pg_hba.conf
    #add
    host    replication     repuser         0.0.0.0/0               md5
    
    #restart service
    service postgresql-10 restart
    service postgresql-10 status
    
    
    export PGHOST="127.0.0.1"
    export PGHOME=/home/pgdata/pg10
    export PATH=$PGHOME/bin:$PATH
    export PGPORT=5432
    export PGDATABASE=postgres
    export PGDATA=$PGHOME/pgdata
    export PGPASSWORD=123456
    export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

## create user on master
    
    CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';
    
## configure salve
    
    
    pg_basebackup -D $PGDATA -F p -h 172.104.106.150 -p 5432 -U repuser -W
    password
    
    ./pg_basebackup -D /usr/local/postgres10/pgdata -F p -h 10.46.135.66 -p 21769 -U repuser -W
    
    ./pg_upgrade -b /usr/local/pg951/bin -B /usr/local/postgres10/bin -d /usr/local/postgres10/pgdata1 -D /usr/local/postgres10/pgdata
    
    
     
    cp /usr/pgsql-10/share/recovery.conf.sample /var/lib/pgsql/10/data/recovery.conf
    vi /var/lib/pgsql/10/data/recovery.conf
    standby_mode = on
    trigger_file = '/home/pgdata/pg10/pg.trigger'
    primary_conninfo = 'host=172.104.101.200 port=5432 user=repuser password=123456 keepalives_idle=60'

# Install pgpool

## Install from repository

yum install http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-release-3.6-1.noarch.rpm
    yum install pgpool-II-10
    yum install pgpool-II-10-extensions
    
    systemctl enable pgpool-II-10.service #/usr/lib/systemd/system/pgpool-II-10.service
    systemctl start  pgpool-II-10.service
    #systemctl stop pgpool.service 
    chkconfig pgpool-II-10 on

## Install from source

wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.1.tar.gz
    tar zxvf pgpool-II-3.6.1.tar.gz
    cd pgpool-II-3.6.1
    ./configure --prefix=/usr/local/pgpool361 --with-openssl --with-pgsql=/usr/local/postgres10 
    make 
    make install
     
    
    cd src/sql/pgpool-recovery/
    make
    make install
    
    psql template1
    CREATE EXTENSION pgpool_recovery;
    
    cd src/sql/pgpool-regclass
    make
    make install
    
    vi postgresql.conf
    pgpool.pg_ctl = '/usr/local/pgsql/bin/pg_ctl'

## Configure pool

All the Pgpool-II configuration files live in /etc/pgpool-II

chown -R postgres:postgres  /etc/pgpool-II-10/
    cd /etc/pgpool-II-10/
    pg_md5 -u pgpool -p #123456
    
    vi pcp.conf
    #add
    pgpool:e10adc3949ba59abbe56e057f20f883e
    
    
    cp pgpool.conf.sample-stream pgpool.conf
    vi pgpool.conf
    
    listen_addresses = '*'
    port = 21767
    socket_dir = '/home/pgapp/master-slave'			
       
    pcp_listen_addresses = '*'
    pcp_port = 9898
    pcp_socket_dir = '/home/pgapp/master-slave'		
    	   
    backend_hostname0 = 'db1'
    backend_port0 = 21768
    backend_weight0 = 1
    backend_data_directory1 = '/usr/local/pgsql/pgapp'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = 'db2'
    backend_port1 = 21768
    backend_weight1 = 10
    backend_data_directory1 = '/usr/local/pgsql/pgapp'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    enable_pool_hba = on
    pool_passwd = 'pool_passwd'
    
    
    num_init_children = 16
    max_pool = 1
    child_life_time = 600
    
    log_connections = on
    log_hostname = on
    log_statement = on
    log_per_node_statement = off
    log_standby_delay = 'none'
    syslog_facility = 'LOCAL0'
    syslog_ident = 'pgpool-pgapp'
    
    
    pid_file_name = '/home/pgapp/master-slave/pgpool.pid'
    logdir = '/home/pgapp/master-slave/'
    
    
    insert_lock = on
    
    load_balance_mode = off
    
    master_slave_mode = on
    master_slave_sub_mode = 'stream'
    sr_check_period = 5
    sr_check_user = 'repuser'
    sr_check_password = '123456'
    sr_check_database = 'postgres'
    delay_threshold = 16000
    
    health_check_period = 5
    health_check_timeout = 20
    health_check_user = 'repuser'
    health_check_password = '123456'
    health_check_database = 'postgres'
    health_check_max_retries = 3
    health_check_retry_delay = 1
    connect_timeout = 10000
    failover_command = '/home/pgdata/pgpool375/etc/failover_stream.sh %H'
    failback_command = ''

-----------------------
    
    vi failover_stream.sh
    
    #! /bin/sh
    # Failover command for streaming replication.
    # Arguments: $1: new master hostname.
    
    new_master=$1
    trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
    
    # Prompte standby database.
    /usr/bin/ssh -T $new_master $trigger_command
    exit 0;
    
    ------------------

cp pool_hba.conf.sample pool_hba.conf
    vi pool_hba.conf
    #modify
    host    all    all    0.0.0.0/0    md5
    
    
    select passwd from pg_shadow where usename = 'postgres'; 
    vi pool_passwd
    #user:password_md5
    postgres:[select passwd from pg_shadow where usename = 'postgres']
    
## Start pgpool

mkdir /var/log/pgpool
    chown postgres.postgres /var/log/pgpool
    mkdir /var/run/pgpool
    chown postgres.postgres /var/run/pgpool
    
    pgpool -n -f /etc/pgpool-II-10/pgpool.conf -F /etc/pgpool-II-10/pcp.conf -a /etc/pgpool-II-10/pool_hba.conf -D >> ../logs/
    
    
## show status

./psql -p xxxx -U pgdata -h x.x.x.x
    show pool_nodes

#Continuous Archiving and Point-in-Time Recovery (PITR)

##Setting Up WAL Archiving

To enable WAL archiving, set the wal_level configuration parameter to archive or higher, archive_mode to on, and specify the shell command to use in the archive_command configuration parameter. In practice these settings will always be placed in the postgresql.conf file. In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name. (The path name is relative to the current working directory, i.e., the cluster's data directory.) Use %% if you need to embed an actual % character in the command. The simplest useful command is something like:

```
wal_level=hot_standby
archive_mode=on
archive_command='sh /pgdata/shell_script/dbbackups/bin/archive_command.sh %p %f'
archive_timeout=60
```

##Making a Base Backup

The easiest way to perform a base backup is to use the pg_basebackup tool. It can create a base backup either as regular files or as a tar archive. If more flexibility than pg_basebackup can provide is required, you can also make a base backup using the low level API

./backup_database.sh database_dev

##Recovering Using a Continuous Archive Backup

https://www.postgresql.org/docs/9.5/continuous-archiving.html

./pitr_recovery.sh database_dev '2019-09-18 18:03:00+08'

Navigation

Recent Posts

Friend Links