PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby and so on. It can be used to power anything from simple web applications to massive databases with millions of records.

Client Installation

If you only wish to connect to a PostgreSQL server, just install the PostgreSQL client:

1

sudo apt-get install postgresql-client

You then connect to the server with the following command:

1

psql -h <server> <database> <user>

Server Installation

To install use the command line and type:

1

sudo apt-get install postgresql

If you are a beginner, pgAdmin is helpful for you. It’s a handy GUI for PostgreSQL:

1

sudo apt-get install pgadmin3

Basic Server Setup

To start off, we need to change the PostgresSQL postgres user password:

1
2

sudo -u postgres psql postgres
\password postgres

You then give your password when prompted and type Control-D to exit.

Create Database

Before creation, create a user as the owner of the first database:

1

sudo -u postgres createuser -P tester

Then create a password for the user:

1
2

sudo -u postgres psql
\password tester

To create the first database, which we will call “mydb”, simply type:

1

sudo -u postgres createdb mydb -O tester

Certainly, you can drop it like this:

1

sudo -u postgres dropdb mydb

Inspect Database

Get help:

1
2

mydb=# \?
mydb=# \h

Show version:

1

mydb=# SELECT version();

Change database:

1

mydb=# \c [database]

Show databases:

1

mydb=# \l

Show tables:

1

mydb=# \dt

Show table columns:

1

mydb=# \d [database]

Read commands from file:

1

mydb=# \i [file]

Quit:

1

mydb=# \q

Backup Database

Dump a database:

1

sudo -u postgres pg_dump [database] > [file]

Restore a database:

1

sudo -u postgres psql -d [database] -f [file]