Lampros - Weird Bricks

PostgreSQL 9.3 Installation on CentOS

24 June, 2016 | PostgreSQL

A long time ago I wrote a post on how to install MySQL on FreeBSD. Lately I've had to work on more PostgreSQL installations so I've been trying to get more familiar with it, so I'm currently working on a series of posts on how to do the basics.

Here I go through the steps of installing PostgreSQL 9.3 on CentOS in a VPS.

1. Install repos

yum -y install https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-2.noarch.rpm

2. Check your available packages:

yum --disablerepo=* --enablerepo=pgdg93 list | grep postgresql | grep server

Output:

postgresql93-server.x86_64 9.3.13-1PGDG.rhel6 @pgdg93

3. Install the Postgres server:

yum -y install postgresql93-server

4. Initialize the server:

service postgresql-9.3 initdb

Output:

Initializing database: [ OK ]

5. Start the server:

service postgresql-9.3 start

Output:

Starting postgresql-9.3 service: [ OK ]

6. Let's become the postgres user:

su - postgres

7. Start the Postgres interactive terminal `psql`:

psql

Output:

psql (9.3.13)
Type "help" for help.

postgres=#

8. Create a test database:

postgres=# create database lampros;

Output:

CREATE DATABASE

9. List databases - \l

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
lampros | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

10. Connect to the database with \c lampros

postgres-# \c lampros

Output:

You are now connected to database "lampros" as user "postgres".

11. Create a table called "little_sheep" with two fields: a "name" and "lastname":

CREATE TABLE little_sheep (name VARCHAR(20), lastname VARCHAR(20) );

Output:

CREATE TABLE

12. List the tables with \d

lampros=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | little_sheep | table | postgres
(1 row)

13. To describe the table:

Use:

\d+ little_sheep

Output:

\d+ little_sheep
Table "public.little_sheep"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------+-----------+----------+--------------+-------------
name | character varying(20) | | extended | |
lastname | character varying(20) | | extended | |
Has OIDs: no

14. Now let's add a couple of sheep:

insert into little_sheep (name,lastname) values ('lampros','sheepah');
insert into little_sheep (name,lastname) values ('stelios','sheepah');

Output:

lampros=# insert into little_sheep (name,lastname) values ('lampros','sheepah');
INSERT 0 1
lampros=# insert into little_sheep (name,lastname) values ('stelios','sheepah');
INSERT 0 1

15. Let's list our sheep:

Use:

select * from little_sheep;

Output:

lampros=# select * from little_sheep;
name | lastname
---------+----------
lampros | sheepah
stelios | sheepah
(2 rows)

16. Finally let's drop the little_sheep table:

Use:

drop table little_sheep;

Output:

lampros=# drop table little_sheep;
DROP TABLE

17. Confirm that it's gone:

\d
No relations found.

18. Note that by default Postgres is only listening locally:

netstat -ntlp | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1518/postmaster
tcp 0 0 ::1:5432 :::* LISTEN 1518/postmaster

In the next post I'll demonstrate how to edit the configuration to make Postgres listen for remote connections.