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 myuser, mydb and lmpassword.
sudo -u postgres psql -c "CREATE USER myuser WITH PASSWORD 'lmpassword'";
sudo -u postgres createdb --encoding=UTF8 -O myuser mydb
sudo -u postgres createdb --encoding=UTF8 -O myuser mydb
Import Database
Say you have mydb.pgsql file with you. To import it to database, use below command.
psql -U myuser mydb < mydb.pgsql
Export Database
If you have a postgresql database with user name as myuser, db name as mydb, you can export the entire database using below command.
pg_dump -U myuser mydb > mydb.pgsql
Some Useful Commands
Go to postgres user
sudo -i -u postgres
Psql prompt
psql
Connect to database
\c mydb
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
Assign postgres permsissions to normal user
Grant the postgres user permissin to normal user say myuser. This helps lm controller to connect to db server.
grant postgres to myuser;
Connect from a machine to remote DB server
From my local machine I can connect to a remote db server using below command from terminal.
psql postgres://dbuser:passwod@dbserverip:dbserverport/dbname
psql postgres://myuser:****@192.168.1.5:5432/mydb
psql postgres://myuser:****@192.168.1.5:5432/mydb
Or alternatively you can give the password when asked, for that use below command.
psql postgres://dbuser@dbserverip:dbserverport/dbname
psql postgres://myuser@192.168.1.5:5432/mydb
psql postgres://myuser@192.168.1.5:5432/mydb
Then system will ask for password and enter the myuser password.
Change the sequence number for a table
If you have a column type as serial, then it keeps incrementing the column value as records are added to table.
If you delete the records and want to change the sequence value use below example.
select setval('users_uid_seq', 3);
This sets uid column of users table to 3.