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
Understand Deep Learning and how it works with a simple example

Deep Learning is a part of Machine Learning, it mimics the way our brains fundamentally work. We all know that our brain consists of billions …

Continue Reading...
Letsencrypt wildcard - Setup wildcard subdomain using letsencrypt and certbot

To get wildcard supported certificates, we need to pass the challenge which requires adding TXT records in your dns records.
To get certificates for single domains, …

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...
How to setup http password authentication with nginx

HTTP Authentication is used to allow access limit to a site or particular directories by validating the username and password. HTTP basic authentication can also …

Continue Reading...
Gitlab and Docker - Continuous Integration, Deployment and Continuous Delivery

In this blog post, we will learn how to setup and use gitlab container registry to implement, test build and deploy your code automatically with …

Continue Reading...
How to backup and restore mysql, postgresql and mongodb databases

Data loss can happen when we accidentally delete the files, or when server crashes or system fails, or when we applied migrations to the data …

Continue Reading...
Kubernetes Installation on BareMetal(Fedora)

Kubernetes manages containerized applications across multiple hosts. With years of experience in managing highly scalable products, google has released kubernetes an open source project which …

Continue Reading...
php7 hosting on Ubuntu Server with Nginx(LEMP stack)

PHP7 is the latest version with lot of performance improvements and every one want to update their servers and code to it. Lets see how …

Continue Reading...
Clustering of Docker Containers using Docker Swarm

There are many available ways of Docker clustering. Kubernetes, Docker cloud, Docker Swarm etc., In this Tutorial we will learn setting up a cluster using …

Continue Reading...
Setting Up Gitlab container Registry on own Domain.

GitLab Container Registry is a secure and private registry for Docker images integrated completely in Gitlab. In this tutorial we will setup and use GitLab …

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...
Understanding Logstash Parsing Configurations and options

In This Tutorial we will learn to customize Logstash to parse any type of Log Files. Logstash helps us to process logs and other event …

Continue Reading...
How to Deploy Django Project into Docker Container

Docker, has captured the hearts and minds of the Devops community, with its platform for delivering distributed applications in containers. In this Blog Post, Lets …

Continue Reading...
Django Testing Automated with Self Hosted Gitlab CI and Docker

It is a bit pricy if you want to host code for collaboration using bitbucket or github on your own VPS or On-Premisis servers. Gitlab …

Continue Reading...
How to deploy django with uwsgi and nginx using Ansible play book

Ansible is a scripting language mostly used to automate installing applications, deploying apps etc..
Why Ansible:
You may think why learn new language if I can do …

Continue Reading...
Setting Up Sentry - Web Application Event Tracking platform

When you want to track your exception and log mesages in a UI rather than storing it in a file(which we usually do), we can …

Continue Reading...
Configure SSL with LetsEncrypt and nginx

Configuring SSL is beneficial not only for security purpose but also for SEO too.
Linuxsoftware foundation's initiated a program called Let’s Encrypt to give ssl certificates …

Continue Reading...
Automate Django Deployments with fabfile

Fabric is a Python library and command-line tool with the ability to execute commands on a remote server. It is designed to use SSH to …

Continue Reading...
Ansible for Server Process Automation

Ansible is one of the few automation tools which uses an agentless architecture, where you dont need to install daemons in client nodes for interacting …

Continue Reading...
Ansible Galaxy Introduction.

Ansible Galaxy is the hub of ansible scripts contributed by users. To follow this article its important that you know about ansible. We have a …

Continue Reading...
Securing Elastic search Instance with Shield

Shield allows you to protect elastic search data with a username and password. It provides additional functionalities like encryption, role-based access control, IP filtering. It …

Continue Reading...
ELK Stack for Parsing your Logs- Part 2

In Previous Tutorial we looked into Setting up EL Server which can ingest your Syslog files. In this Post, you will learn about pushing your …

Continue Reading...
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...
Web Hooks for Gitlab using PHP and Shell Scripts

Web-hooks play vital role if you are in Continuous Integration(CI). Higher Level organizations follow GitLab for CI purposes if they operate on open source solutions …

Continue Reading...
Elastic Search Security Measures

After Installing Elastic Search in production, many new developers leave it unconfigured. In this Post, we will look into important config file changes and its …

Continue Reading...
Configuring WordPress Blog as sub-directory alongside Django in Nginx

Using regular expressions within Nginx we can bind urls to particular application servers, below we will configure wordpress blog and django site to be deployed …

Continue Reading...
How to Do Page Redirections with Nginx.

Here we learn how to redirect a www to non-www or vice-versa also we see how to redirect pages. We will see differences between 301 …

Continue Reading...
Basics of Linux File System Heirarchy

In 1994, FHS(File System Hierarchy Standard) was proposed, it describes the directory structure of UNIX and UNIX-like systems(Linux). Before FHS was proposed, there had been …

Continue Reading...
Postgresql Installation and management basics.

PostgreSQL or simply postgres is the most advanced, SQL-compliant and open-source objective-RDBMS. In This Article, you will learn how to Install, Connect and Manage a …

Continue Reading...
Fabric - Learning part1

Using Fabric, we can configure Linux production server with uwsgi, nginx, vsftpd, mysql, mongodb, postfix, php, python tools, and other relevant pieces of software single-handedly …

Continue Reading...
MySQL Setup and Security

In This Post you'll learn to install and secure your MYSQL Setup to interact with external applications.

Continue Reading...
TMUX - Sharing terminal between Users

More Often you wouldve come across sharing screens with applications like Teamviewer, Windows Remote Desktop Connection or remmina (if you're a linux user). But if …

Continue Reading...
Django hosting on Nginx with uwsgi for high performance

Quick guide to host django application using uwsgi and nginx which offers better resource utilization and performance.

Continue Reading...
Celery With Supervisor

Celery is a task queue with focus on real-time processing, while also supports task scheduling. Task queues are used as mechanisms to distribute work across …

Continue Reading...
Django on GAE (google app engine)

Google App Engine (often referred to as GAE or simply App Engine) is a platform as a service (PaaS) cloud computing platform for developing and …

Continue Reading...
Daemonizing any command with SUPERVISOR

Daemonizing a command means to make it run as a background process.

so when we have a command that we want to daemonize there are …

Continue Reading...

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