This scripts will create postgres database with specified owner and resore a sql dump in to it
#The super user postgres login along woth the password ‘admin’ in psql console
$psql -h localhost -p 5432 <postgres> “admin”
#creating a database named ‘testdb’
postgres=# CREATE USER testuser;
postgres=# ALTER USER testuser PASSWORD ‘letmein’;
#create a database named ‘mydb’ owned by the given user
postgres=# CREATE DATABASE mydb WITH OWNER testuser;
#restoring dump to that database
postgres=# source<path/to/sql file>;
Some external commands
1, Dumping only data
$pg_dump -a -d <DB> -f <Filename.sql> -U <user> -p <passwd>
2, Dump only database schema
$pg_dump -s -d <DB> -f <Filename> -U <user> -p <passwd>
3, Complete database dump
$pg_dump -d <DB> -f <Filename> -U <user> -p <passwd>
4, To dump all databases
$ pg_dumpall > db.out
5, To reload this database use, for example:
$ psql -f db.out postgres
6, List the databases
$psql -l
Leave a Reply