PostgreSQL – AWS Union Makes Force

If you clicked on this article is because according to what you are involved, you are interested in the type of database PostgreSQL because:

  • It is a free software
  • It makes hot Backups
  • It has encrypted access via SSL
  • It is available for Linux and UNIX in all its variants
  • It has MVCC (Multiversion Concurrency Control System) and it can interact several processes in a table without being blocked
  • It allows to manage geolocation and positioning data with the PostGIS tool.
  • You can manage in combination from version 9.4, a type of SQL database (Relational) with non-SQL natively JSON (key-value used by Mongodb).

Regardless of:

  • Being slower, but more reliable than MySQL

Well, if after these brief explanations you decide to continue, this is your blog post!

 

What are we going to do?

We will set up  a PostgreSQL service, both locally and in AWS (Amazon Web Services) with RDS (Relational Database Service), which we will attack remotely.

LOCALLY

We will use a machine with Debian GNU / Linux 8.9 operating system, I particularly used this set up for a staging environment.

 

 

We will install the version that comes by default, then check the version and see that the process is raised on port 5432:

 

Here our beloved log:

 

Once we have the service up and before putting starting with the management of users and databases, we still have a pending issue with PostgreSQL.

 

By default PostgreSQL in Debian GNU / Linux leaves the PEER authentication system enabled for local connections, so if we wanted to connect to a database with a system user that we did not create in a database, it will give us many errors:

 

Let’s take a look at our log:

 

In order to solve this, we will change the authentication type by MD5 (password-based authentication).

 

To do this, copy and edit the file pg_hba.conf with the user postgres:

 

Then check the change and restart the service:

 

¡Now it is working!

 

Another thing to keep in mind of the PostgreSQL shell is that the commands are terminated with semicolons, if they are not, it will not report any errors and the prompt is left with postgres- #, giving us some headache:

 

This is the help and output of the PostgreSQL shell:

 

We can launch commands from both the system shell and the PostgreSQL shell, find below some simple examples:

 

Creation of database and sample table:

 

List of the table and fields created (you must pay close attention to the punctuation marks):

 

With a postgres user we can make a general list of users and database:

 

Change user password:

 

Change of database owner:

 

Dump y restore of the database (the postgres user we will be able to interact, without having to reference the user who owns the database):

 

Erasing database and user

 

Well, now it is time to open a web browser since we are heading to…

AWS (Amazon Web Services) -> RDS (Relational Database Service)

In this section you need some experience in AWS administration, it will be asumed that some concepts will be already acknowledged.

First, we log in into our AWS account> Services> RDS> Instances> Launch DB Instance:

 

AWS - Gigigo

 

PostgreSQL - Gigigo

 

For our test we will only use a simple instance:

 

We take the version of PostgreSQL that is more similar to the one we have installed locally. Afterwards, we choose our user name database (later on, we will stop at the permissions that AWS assigns us regarding this user) and password:

especificaciones de instancias - Gigigo

 

 

We continue with more configurations, you just need to adapt them to your needs:

advanced settings - Postgre - Gigigo

 

Attention to the VPC Security Group, in this case we give access to port 5432 only to the ip of the host where the PostgreSQL client is:

 

 

 

postgresql_up - Gigigo

 

Attention: Automatic snapshots disappear when we delete the instance, so:

  • Create a manual one before deleting it.
  • If you need to lift one of the previous days, raise it with another name etc … and when it is ok, you can delete the current one.

 

Once we have it available we can manage it through the menu Instance Actions:

 

Copy the endpoint you have generated and access:

 

We can now start working with the advantage of using the possibilities offered by an RDS instance with PostgreSQL in AWS

(https://aws.amazon.com/es/rds/postgresql/).

 

Do you want more?

Well, you should pay attention, since this can be useful…

One day we needed to import a csv file into a database table, but with the user created by default it gave an error because it did not have enough power.

Let’s see what happened in both the staging environment where we had set up PostgreSQL in local, as in remote with RDS.

LOCAL

This is the error and its solution (there were no properties that would enable the import, it must be superuser):

 

AWS

Here things already change, since the properties of the user Username Master are:

 

We see that it is NOT a user with attributes of the superuser, but it is a member of rds_superuser, so the COPY “to dry” of the previous case with PostgreSQL in local is not valid.

 

Finally, after performing several tests of all kinds at the end we use:

\ COPY, since it was necessary to be embedded inside the system shell with the psql client (again, you must pay close attention to punctuation):

References to the case:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

To write this article I have relied on the following information:

https://www.postgresql.org/docs/9.4/static/index.html

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

 

This Blog Post wouldn’t have been possible without the collaboration of:

  • Mónica González – Communications Department
  • Belén De Pablo – UX/UI Department

Infrastructure Department.