Postgresql Installation and management basics

PostgreSQL or simply postgres is the most advanced, SQL-compliant and open-source objective-RDBMS. Compared to other RDBMSs, PostgreSQL differs itself with its support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID).In This Article, you will learn how to Install, Connect and Manage a postgres database.

Installing & Managing postgresql Database in Debian:

sudo apt-get install postgresql-9.5 libpq-dev

This will install Database server, its dependancy packages and Library Files. This will also create a default user for postgresql called postgres.

Connecting to Database & initial setup:

# connecting to database
sudo su
su postgres -c "psql"
# change postgres user password
ALTER USER postgres PASSWORD 'our-new-password';
\q
# modify pg_hba.conf to connect to database with password only.
local all postgres peer
local all all peer
change peer to md5 and restart postgresql for changes to take affect.
# connecting to database 
psql -U postgres

enter password when prompted and you will be connected to databse.

Basic Commands:

# create new database
CREATE DATABASE gondor;
# create new user
CREATE USER elessar WITH PASSWORD '<some-password>'
# grant the user permission to Database
GRANT ALL PRIVILEGES ON DATABASE gondor TO elessar;
# switch to database
connect <database-name>

Using Docker Image:

Installing Docker:

wget -qO- https://get.docker.com/ | s

Deploy postgres container:

docker run --name some-postgres -e POSTGRES_PASSWORD=<password-postgres-user> -d postgres

Replace <password-postgres-user> and run the command. This will launch postgresql database.

Connecting to this Docker Instance:

apt-get install postgresql-client-common postgresql-client-9.5

This will install client tools, then you can connect using

psql -U postgres -h <docker-ip>

This will prompt for password and then you can connect and use postgres as you normally would.

The article was originally published at MicroPyramid blog.