PostgreSQL Database Import

Introduction

PostgreSQL is a SQL like database platform. Most of the SQL syntax works, however in rare cases you need to tweak in syntax to get desired result. However here the scope is how to install postgresql in Ubuntu 14, create database, user and import an existing posgresql file.


Install PostgreSQL

To install postgresql make use of below commands.

sudo apt-get install postgresql postgresql-contrib

With successful installation of above two packages, postgresql is installed in your Ubuntu 14 system.


Create User and Database

To create user and database, make use of below commands. Please note that user, database and password are lmuser, lmdb and lmpassword.

sudo -u postgres psql -c "CREATE USER lmuser WITH PASSWORD 'lmpassword'";
sudo -u postgres createdb --encoding=UTF8 -O lmuser lmdb



Import Database

Say you have lmdb.pgsql file with you. To import it to database, use below command.

psql -U lmuser lmdb < lmdb.pgsql



Export Database

If you have a postgresql database with user name as lmuser, db name as lmdb, you can export the entire database using below command.

pg_dump -U lmuser lmdb > lmdb.pgsql



Some Useful Commands
Go to postgres user
sudo -i -u postgres
Psql prompt
psql
Connect to database
\c lmdb
See all tables
\dt



Create user, database & grant access

Use below sequence of commands to create user, database and grant access on it.

sudo -i -u postgres
psql
create database mydatabase;
create user myuser with password 'mypassword';
grant all privileges on database mydatabase to myuser;



Reset database

Best way is to delete database and create again. You can make use of below commands to drop a database. Then you can create the database as shown in above section.

sudo -i -u postgres
psql
drop database mydatabase;



Reset serial column in a table

When you truncate a table, you may like to reset serial column so that any new record added, serial column value should start from 1. To do that follow below sequence of commands.

sudo -i -u postgres
psql
truncate table mytable restart identity;