PostgesSQL 9 Administration QuickRef(2) - Managing Database

2015-02-21 by terryoy, in tricks

1. Creating a Database

It's a common task to create a database and assign an access user for it. This can be done by command line or by psql queries.

# by command line
$ su postgres
$ createuser -D -A -P myuser
$ createdb -O myuser mydb

# (or) a longer version for the createdb command
$ createdb -h localhost -p 5432 -U postgres -O myuser -E UTF8 mydb
--create database by psql client 
postgres=# CREATE USER myuser WITH PASSWORD 'my_password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE "mydb" to myuser;

2. Configuring Connection Method

Peer authentication uses system's user accounts for authentication, while MD5 authentication uses password authentication encrypted in md5.

Restart Postgresql service after configuration file is changed.

# Edit the file for the database access method
$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres            md5
#host    replication     postgres        ::1/128                 md5

$ sudo service postgresql restart

3. Migrating Data

When migrating database, usually we use migrate files with SQL and CSV format. Since SQL scripts are probably not compatible with different database provider(e.g. postgresql vs mysql), sometimes it's easier to parse data with CSV format, while SQL files are convenient to dump and import schemas between postgresql databases.

3.1 Export data to CSV files

Note: the =# prefix is the prompt of psql command line tools.

--Export all records from table to CSV file
=# \copy my_table to '/path/to/table.csv' csv header;
=# COPY my_table TO '/path/to/table.csv' DELIMITER ',' CSV HEADER;

--Export a custom mquery to csv
=# COPY (select * from public.user) TO '/path/to/user.csv' CSV HEADER;

3.2 Export schema and data to SQL files

We'll use pg_dump to create postgresql export files.

# sometimes you need to specify which database version to use, due to the version mismatch of pg_dump program
$ pg_dump --cluster 9.3/main <...other stuffs>

# export schema with "-s" or "--schema-only"
$ pg_dump -s <db_name> > export_schhema.sql
$ pg_dump --schema-only <db_name> > export_schema.sql

# export data
$ pg_dump -a <db_name> > export_data.sql
$ pg_dump --data-only <db_name> > export_data.sql