#postgresql

Linux

PostgreSQL Command Line

2

There are many different between MYSQL and PostgreSQL. One of them is PostgreSQL is Command Lines.  I write as below for the reference as needed.

70f45f89-72f1-456a-a952-86e2910d04f2-medium

What motivates me? The fear of being average

Show databases

1
2
3
\l
#or
\list

Connect to database

1
\connect database_name

List all tables

1
\d+

Show table schema

1
\d+ tablename

Dump a table

1
pg_dump --username postgres -f /tmp/table_name.sql -d database_name -t table_name

Import a table or database:

1
pgsql database_name < file_name

Time comparison:

1
SELECT * FROM users WHERE created_at >= '{}'::date AND created_at < ('{}'::date + '1 day'::interval)

 

Nguyen Sy Thanh Son

2

Linux

Backup Postgres 9.4 to S3 with WAL-E in Ubuntu 14.04

5

If you are using Postgres 9.4 Database for your project. I think that you are thinking about backup Backup Postgres everyday. So in this post I will show you how to  backup Backup Postgres 9.4 to S3.

9efd1a62-cdf7-4a05-8d2f-11e0b44492c8-medium

A sign of a good leader is not how many followers you have, but how many leaders you create.

Install Dependencies:

1
apt-get install lzop pv python-pip python daemontools

Using PIP to install WAL-E:

1
pip install wal-e

Using PIP to upgrade Request:

1
pip install -U requests

Using PIP to upgrade Six:

1
pip install --upgrade six

If you not upgrade them,  maybe you will meet an error as below when you run WAL-E Backup:

1
2
3
4
5
6
7
8
9
10
11
12
13
$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.4/main
Traceback (most recent call last):
  File "/usr/local/bin/wal-e", line 5, in <module>
    from pkg_resources import load_entry_point
  File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 2749, in <module>
    working_set = WorkingSet._build_master()
  File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 446, in _build_master
    return cls._build_from_requirements(__requires__)
  File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 459, in _build_from_requirements
    dists = ws.resolve(reqs, Environment())
  File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 628, in resolve
    raise DistributionNotFound(req)
pkg_resources.DistributionNotFound: six>=1.9.0

And we should change permission for PIP packet so that postgres user is able to use them:

1
2
$ chmod a+xr -R /usr/local/lib/python2.7/dist-packages/requests*
$ chmod a+xr -R /usr/local/lib/python2.7/dist-packages/six*

Edit postgresql.conf to do backup with wall-push command:

1
2
3
4
wal_level = archive # hot_standby in 9.0+ is also acceptable
archive_mode = on
archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
archive_timeout = 60

Now, we restart postgres to apply the changes:

1
/etc/init.d/postgresql restart

Backup Everyday

Assume that you created a bucket on S3, and you have AWS credentials. So you should push them to config file with commands:

1
2
3
4
5
6
7
umask u=rwx,g=rx,o=
mkdir -p /etc/wal-e.d/env
echo "your_aws_secret_key" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo "your_aws_access_key_id" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo 's3://yourbucketname/postgres' > \
  /etc/wal-e.d/env/WALE_S3_PREFIX
chown -R root:postgres /etc/wal-e.d

Now, we will try to backup to S3 in the first time. At the first, change to postgres user:

1
sudo su postgres

Run backup command:

1
$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.4/main

The output should be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
wal_e.main   INFO     MSG: starting WAL-E
        DETAIL: The subcommand is "backup-push".
        STRUCTURED: time=2016-01-05T02:20:47.435213-00 pid=11384
wal_e.operator.backup INFO     MSG: start upload postgres version metadata
        DETAIL: Uploading to s3://xxxxxxxxxxxxxxxxxx/postgres/basebackups_005/base_000000010000000000000002_00000040/extended_version.txt.
        STRUCTURED: time=2016-01-05T02:20:47.715716-00 pid=11384
wal_e.operator.backup INFO     MSG: postgres version metadata upload complete
        STRUCTURED: time=2016-01-05T02:20:47.786793-00 pid=11384
wal_e.worker.upload INFO     MSG: beginning volume compression
        DETAIL: Building volume 0.
        STRUCTURED: time=2016-01-05T02:20:47.826936-00 pid=11384
wal_e.worker.upload INFO     MSG: begin uploading a base backup volume
        DETAIL: Uploading to "s3://xxxxxxxxxxxxxxx/postgres/basebackups_005/base_000000010000000000000002_00000040/tar_partitions/part_00000000.tar.lzo".
        STRUCTURED: time=2016-01-05T02:20:48.233511-00 pid=11384
wal_e.worker.upload INFO     MSG: finish uploading a base backup volume
        DETAIL: Uploading to "s3://xxxxxxxxxxxxxxxxxx/postgres/basebackups_005/base_000000010000000000000002_00000040/tar_partitions/part_00000000.tar.lzo" complete at 9791.88KiB/s.
        STRUCTURED: time=2016-01-05T02:20:48.846993-00 pid=11384
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

Finally, we add the command to crontab to backup 5 AM everyday:

1
0 5 * * * /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.4/main

Finished your works now!

Nguyen Sy Thanh Son

5

Linux

Replication and Load Balance in PosgreSQL 9.4 with PgPool2

11

When you design a scale, high availability, high performance system, I sure that you have to apply Replication, Sharding and Load Balance technologies for your database layer. In this post, I will show you the way how to setup a load balance with PgPool2 for your PostgreSQL Master-Slave Replication.

My scenario as image below:

pgpool (1)

Step 1: Install PostgreSQL Replication for your servers

At first, Please refer to 2 posts:

 

After that, install Pgpool Lib:

1
sudo apt-get install postgresql-9.4-pgpool2

Step 2, Install PgPool2 for server 192.168.1.3

1
sudo apt-get install pgpool2

Edit /etc/pgpool2/pgpool.conf as below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# - Backend Connection Settings -
 
backend_hostname0 = '192.168.1.4'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/postgresql/9.4/main/'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '192.168.1.5'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.4/main/'
backend_flag1 = 'ALLOW_TO_FAILOVER'
 
# - Authentication -
 
enable_pool_hba = on

Make sure you set enable_pool_hba = on

And change /etc/pgpool2/pool_hba.conf to enable md5 for authentication:

1
host    all         all         192.168.1.0/24        md5

Add account to /etc/pgpool2/pool_passwd.conf (just a example)

1
postgres:md5xxxxxxxxxxxxxxxxxxxxxxxxxxx

Now, run pgpool with log:

1
pgpool -n -d > /tmp/pgpool.log 2>&1 &

And stop Pgpool if you need:

1
pgpool stop

Finally, test your installation. Good Luck!

Nguyen Sy Thanh Son

11

Linux

Replication Master-Slave with PostgreSQL 9.4 in Ubuntu 14.04

10

My scenario is I have two servers at addresses 192.168.1.191 and 192.168.1.171, and installed Ubuntu 14.04 and PostgreSQL 9.4.

To know how to install PostgreSQL, please read Install PostgreSQL 9.4 in Ubuntu 14.04

Step 1: Make sure that the two servers are able to SSH freely between them

Switch to Postgres account:

1
sudo su - postgres

Generate ssh key:

1
ssh-keygen

Copy SSH key to opposite server:

1
ssh-copy-id opposite_ip_address

Note: you have to do this step for the both servers 

Step 2: Setup Master Node

Create an account for replication

1
psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"

Modify postgres access config file:

1
vi /etc/postgresql/9.4/main/pg_hba.conf

Add content below:

1
host    replication     rep     IP_address_of_slave/32   md5

And modify postgres config file:

1
vi /etc/postgresql/9.4/main/postgresql.conf

The config value should be:

1
2
3
4
5
6
listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

And now, restart Master server:

1
service postgresql restart

Step 3: Setup Slave Node

Stop PostgreSQL:

1
service postgres stop

Modify postgres access config file:

1
vi /etc/postgresql/9.4/main/pg_hba.conf

Add content below:

1
host    replication     rep     IP_address_of_master/32  md5

And modify postgres config file:

1
vi /etc/postgresql/9.4/main/postgresql.conf

The config value should be:

1
2
3
4
5
6
listen_addresses = 'localhost,<span class="highlight">IP_address_of_THIS_host</span>'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

Step 4: Start the replication

In Master Server, run command below to start the replication

1
2
3
psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"

In Slave Server, create recovery.conf file:

1
vi /var/lib/postgresql/9.4/main/recovery.conf

And add the content below to that file:

1
2
3
standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

And restart Slave Server to complete the setup:

1
service postgresql start

It is time for testing, you can try to create and insert data to Master Server. It will be synchronized to Slave Server

Nguyen Sy Thanh Son

10

Linux

Install PostgreSQL 9.4 in Ubuntu 14.04

4

I am sure that you will hate me when you read this post. Because It is very easy to install PostgreSQL 9.4 in Ubuntu 14.04. However, It works for me. And I hope you too.

Create file  /etc/apt/sources.list.d/pgdg.list, with content:

1
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Import key and update repositories:

1
2
3
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add -
sudo apt-get update

And install PostgreSQL 9.4:

1
sudo apt-get install postgresql-9.4

Finally, test it:

1
sudo -u postgres psql

The result should be:

1
2
3
4
psql (9.4.4)
Type "help" for help.
 
postgres=#

Tips

And try to create a account with database:

1
2
3
4
5
6
CREATE USER thanhson1085 WITH PASSWORD 'jRVAp6tE';
CREATE DATABASE userdb;
GRANT ALL PRIVILEGES ON DATABASE userdb TO thanhson1085;
\connect userdb
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO thanhson1085;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO thanhson1085;

To support remote access with account you just created. You should change file /etc/postgresql/9.4/main/pg_hba.conf.

The below is some commands we usually use in PSQL

Export data to sql file:

1
sudo -u postgres pg_dump -f userdb.sql -d userdb

Import from SQL file

1
sudo -u postgres psql -f userdb.sql userdb

Reset serial number:

1
ALTER SEQUENCE product_id_seq RESTART WITH 1453;

 

Nguyen Sy Thanh Son

4