By continuing to navigate on this website, you accept the use of cookies to serve you more relevant services & content.
For more information and to change the setting of cookies on your computer, please read our Cookie Policy.

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.

    Posted On
  • 08 February 2014
  • By
  • Micropyramid

Need any Help in your Project?Let's Talk

Latest Comments
Related Articles
ELK Stack for Parsing your Logs

In This Tutorial we will look onto parsing your syslog files and store and display it in interactable website. We will be using ELK Stack ...

Continue Reading...
HTTP2 and SPDY Protocols - Make HTTP Faster and Safer

HTTP/2, next version of HTTP/1, http/1 can not handle the present web which has become more resource intensive, it cannot processes multiple requests in an ...

Continue Reading...
Deploying Wordpress blog with Django using Docker Containers.

Containers run services in an isolated environment, if we deploy our applications using containers then the host system on which these containers are running will ...

Continue Reading...
open source packages

Subscribe To our news letter

Subscribe and Stay Updated about our Webinars, news and articles on Django, Python, Machine Learning, Amazon Web Services, DevOps, Salesforce, ReactJS, AngularJS, React Native.
* We don't provide your email contact details to any third parties