Creating a second database from the linux command line

The objective  here was to make a copy of an existing database and bring it up under a new instance. I could in theory have used a different version of the database but at the time I only had one available to me.

Create a new database:

su - root
mkdir /var/lib/postgresql/testinstance
chown postgres:postgres /var/lib/postgresql/testinstance
su - postgres
initdb -D /var/lib/postgresql/testinstance

Starting the Instance

pg_ctl start -D /var/lib/postgresql/testinstance -l /var/tmp/testinstance.log -o "-p 5434"

Check the status:

pg_ctl status -D /var/lib/postgresql/testinstance

Stopping the Instance

pg_ctl stop -D /var/lib/postgresql/testinstance

Create user on new database:

create role timj login password 'xxxxxx';
create database cddb;
alter role timj superuser;
alter role timj nosuperuser;
grant all on database cddb to timj;

Connecting to the database:

psql -d postgres -h localhost -p 5434 -U postgres

Dump the old database:

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F p -v -f "/tmp/mybackup.pgsql" cddb

Restore to new database:

psql -h localhost -p 5434 -U timj -d cddb < /tmp/mybackup.pgsql

Dumping database in custom mode:

/usr/bin/pg_dump -h localhost -p 5432 -U timj -F c -v -f "/tmp/mybackup.pgsqlc" cddb

Use pg_restore if you have a non-ascii backup:

create role timj login password 'xxxxxx';
create database cddb;
grant all on database cddb to timj;
pg_restore -h localhost -p 5434 -U timj -d cddb /tmp/mybackup.pgsqlc