postgress server setup




2017-02-06 09:59:22gstlouis


PostgreSQL, commonly referenced as "postgres", is a popular database management system that uses the SQL querying language to manipulate data.

In this guide, we will cover how to install and use postgres on a CentOS VPS.

How To Install PostgreSQL

Although CentOS contains postgres packages within its default repositories, we will use the packages available from the postgres project itself.

This will ensure that we have access to the latest version of the software without having to install from source.

Exclude CentOS Postgres Packages

Before we install postgres, we must exclude the CentOS version of postgres in order to get the most recent version from the project's website.

Open the CentOS repository configuration file in a text editor:

nano /etc/yum.repos.d/CentOS-Base.repo

At the bottom of the [base] section, add a line that excludes the postgres packages:

[base] name=CentOS-$releasever - Base mirrorlist=$releasever&arch=$basearch&repo=os #baseurl=$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 exclude=postgresql*

Add the same line to the bottom of the [updates] section to prevent yum from updating postgres from the default repositories:

[updates] name=CentOS-$releasever - Updates mirrorlist=$releasever&arch=$basearch&repo=updates #baseurl=$releasever/updates/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 exclude=postgresql*

Add Postgres Repositories

Now, we've told yum to not bother with the default repositories for postgres package interactions.

We need to provide it with an alternative now. We will use the packages provided on the postgres project's website.

Go to the site to find the latest version of postgres available for your CentOS version.

Control-click or right-click on the link that matches the most recent version of postgres and your version of CentOS. Choose "copy link address" or whatever similar option is available.


Back in your droplet session, change to your home directory. Type curl -O and then paste the download link:

cd ~ curl -O

Install the package you just downloaded by typing the following command:

rpm -ivh pgdg*

This will allow your system to pull the postgres package from the project's website.

We can do that now to get the latest version. These packages contain version numbering, so you'll need to search to see what version is being use:

yum list postgres*

Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: * extras: * updates: Installed Packages postgresql93.x86_64 9.3.1-1PGDG.rhel6 @pgdg93 postgresql93-libs.x86_64 9.3.1-1PGDG.rhel6 @pgdg93 postgresql93-server.x86_64 9.3.1-1PGDG.rhel6 @pgdg93 Available Packages postgresql93-contrib.x86_64 9.3.1-1PGDG.rhel6 pgdg93 postgresql93-debuginfo.x86_64 9.3.1-1PGDG.rhel6 pgdg93

We can see that in this case, all of the packages have "93" for version 9.3. We want to download the "-server" packages:

yum install postgresql93-server

Change the "93" to whatever the current version of postgres is for your system throughout the rest of this guide.

Initialize the Database Environment

You can initialize the database environment by typing:

service postgresql-9.3 initdb

We will then configure it to start at boot up and we will start the software:

chkconfig postgresql-9.3 on service postgresql-9.3 start

We are now ready to start using postgres.

How to Log Into the Postgres Database

By default, postgres creates a user and a database called postgres.

The default security scheme assumes that the postgres user will only be accessible to those who need access. This can be controlled by setting up sudo rules.

We interact with the postgres database software through an interface called psql. It provides a prompt where we can manipulate and query for data.

For now, log into the postgres user like this:

su - postgres

Your prompt will change. You can now connect to the database that matches your username by typing:


Your prompt will change again, this time to indicate that you are interacting with the postgres database.

If you need to exit this interface, you can type this at any time:


Afterwards, to get back to the root user shell, you can type:


2017-02-06 09:59:59

postgres by default will not working with making a connection through http.  You will need to change the authentication in the postgres config file 9.6 is the server version
vim /var/lib/pgsql/9.6/data/pg_hba.conf
buttom of the file change ident to md5

local   all             all                                     ident / -> md5


2017-02-06 10:08:58