import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';

Database - Postgres

Installation

yum install postgresql-server postgresql-contrib

apt-get install libpq-dev postgresql postgresql-contrib

Get Started

New Database

Once packages are installed, We can logged with postgres user:

su - postgres

Then the creation of new Database is easily like this:

createdb {db_name}

Prompt psql

It is possible to execute SQL commands directly to interact with the database by running the psql command.

We can now go through this prompt to create a new user to facilitate the administration of our new DB and give all privileges to it:

CREATE USER {DB User} WITH PASSWORD '{Passwd}';
GRANT ALL PRIVILEGES ON DATABASE {DB Name} TO {DB User};

To make sure than the new user is created correctly:

SELECT usename FROM pg_user;

And here is how to change user's password:

ALTER USER yourusername WITH PASSWORD 'yournewpass';

Config files

There are several configuration files:

pg_hba.conf
pg_ident.conf
postgresql.conf

For retrieve location of a configuration file, we can display detail of it from psql prompt.

Example for pg_hba.conf:

postgres=# show hba_file ;
            hba_file
---------------------------------
 /var/lib/pgsql/data/pg_hba.conf
(1 row)

Commands

Globals

List all DB:

\l

Connect to a DB:

\c {db}

List the DB's tables:

\dt

Detail of a table:

\d+

Display active connection:

SELECT pid, datname, usename, state FROM pg_stat_activity

Delete a table:

DROP TABLE {table};

Rename a table:

ALTER TABLE {table} RENAME TO {new_table};

Rename a table's column:

ALTER TABLE {table} RENAME COLUMN {column} TO {new_column};

Change display mode:

\x on

Backup and Restore

See the command for backup database:

pg_dump {db_name} > {out_file}

See the command for restore database:

psql {db_name} < {in_file}

Examples

DB Backup:

$ su - postgres
$ pg_dump my_db > my_db.dump
$ ls -l my_db.dump
-rw-r--r-- 1 postgres postgres 777927 Jul 15 11:54 my_db.dump

Restore DB (With remove of old version):

# su - postgres
$ dropdb
$ createdb my_db
$ psql mydb < my_db.sql
[...]
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
[...]
REVOKE
GRANT
GRANT