Hello World from OSS Silicon Valley

_ Prerequisite

• Ubuntu Server installation (You can refer Overall/UbuntuServer)

_ Install&Setup

Step.1

Install PostgreSQL.

$ sudo apt-get install postgresql pgadmin3

Step.2

Set up the initial password for postgres user.

$ sudo passwd postgres
<password>

Step.3

Setup the initial password for postgres DB user.

$ su - postgres
$ psql
postgres=# alter user postgres with password '<password>';

Step.4

Launch pgAdminIII.

Step.5

Access DB.

_ HowToUse

_ Connect Database

Step.1

Execute the following command.

$ psql -U <User Name> -d <Database Name>

example)

$ psql -U postgres -d sample_development

_ Fundamental commands

(Get User list)

# select * from pg_user;

(Get Database list)

# \l

or

# select * from pg_database;

(Get Table list)

# \d

or

# select * from pg_class;

(Get Table Columns)

# \d <table name>

_ Backup Database

_ SQL dump

Step.1

Backup database with the following command.

$ pg_dump <Database Name> -U <User Name> > <Dump File Name>

Step.2

Restore database with the following command.

$ drop database <Database Name>
$ create database <Database Name>
$ psql <Database Name> < <Dump File Name>

_ File Level Backup

Backup PGDATA directory. In general it is /var/lib/psql/data, but my case is /var/lib/postgresql/9.3/main.

$ tar -cf <backup file name> <PGDATA directory path>

(Example)

$ tar -cf backup.tar /var/lib/psql/data

_ Author

S.Yatsuzuka