#postgresql

PostgreSQL Command Line

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

Connect to database

List all tables

Show table schema

Dump a table

Import a table or database:

Time comparison:

 

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

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:

Using PIP to install WAL-E:

Using PIP to upgrade Request:

Using PIP to upgrade Six:

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

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

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

Now, we restart postgres to apply the changes:

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:

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

Run backup command:

The output should be:

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

Finished your works now!

Replication and Load Balance in PosgreSQL 9.4 with PgPool2

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:

Step 2, Install PgPool2 for server 192.168.1.3

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

Make sure you set enable_pool_hba = on

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

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

Now, run pgpool with log:

And stop Pgpool if you need:

Finally, test your installation. Good Luck!

Replication Master-Slave with PostgreSQL 9.4 in Ubuntu 14.04

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:

Generate ssh key:

Copy SSH key to opposite server:

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

Step 2: Setup Master Node

Create an account for replication

Modify postgres access config file:

Add content below:

And modify postgres config file:

The config value should be:

And now, restart Master server:

Step 3: Setup Slave Node

Stop PostgreSQL:

Modify postgres access config file:

Add content below:

And modify postgres config file:

The config value should be:

Step 4: Start the replication

In Master Server, run command below to start the replication

In Slave Server, create recovery.conf file:

And add the content below to that file:

And restart Slave Server to complete the setup:

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

Install PostgreSQL 9.4 in Ubuntu 14.04

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:

Import key and update repositories:

And install PostgreSQL 9.4:

Finally, test it:

The result should be:

Tips

And try to create a account with database:

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:

Import from SQL file

Reset serial number: