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
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;
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;
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;
psql
truncate table mytable restart identity;
Change postgresql password
Sometimes you need to change the password of postgresql user. You can follow below steps to do that.
sudo -i -u postgres
psql
\password postgres
Enter new password:
Enter it again:
\q
exit
psql
\password postgres
Enter new password:
Enter it again:
\q
exit