postgress server setup
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:
At the bottom of the [base] section, add a line that excludes the postgres packages:
[base] name=CentOS-$releasever - Base mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os #baseurl=http://mirror.centos.org/centos/$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=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates #baseurl=http://mirror.centos.org/centos/$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 http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
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: mirror.linux.duke.edu * extras: mirror.linux.duke.edu * updates: mirrors.kernel.org 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:
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