#postgresql-9.4

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 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: