PostgreSQL

From KevinWiki

Revision as of 15:29, 1 January 2016 by Kevin (Talk | contribs)
Jump to: navigation, search
PostgreSQL
Developer(s) PostgreSQL Global Development Group
Stable release 9.2.2,
9.1.7,
9.0.11,
8.4.15,
8.3.22 /
December 6, 2012; 11 years ago (2012-12-06)
Written in C
Operating system Cross-platform
Type ORDBMS
License PostgreSQL licence[1][2][3]
Website www.postgresql.org

Contents

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS). The documentation of PostgreSQL 8.4.4 can be found here[4]

Installation

  • Install PostgreSQL
$ sudo apt-get install postgresql 

For Latest Version

vim /etc/apt/sources.list.d/pgdg.list 

add

deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main 

e.g.) 14.04

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 

Then import the repository signing key.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add - 

Now update and install.

apt-get update 
apt-get install postgresql 

Administration

Setup

Setup Authentication Methods

  • In order to allow new users to log in the PostgreSQL server, /etc/postgresql/8.4/main/pg_hba.conf should be modified.
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

To

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
#host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
#host    all         all         ::1/128               md5

Allowing the local access only with username & password.


Access Remotely

  • Edit /etc/postgresql/9.1/main/postgresql.conf
listen_addresses = 'localhost,address,separated,by,comma'

The IP is the local one but NOT a remote one. e.g.) 192.168.0.111 means it listens any access to 192.168.0.111 (local IP).

e.g.)

listen_addresses = 'localhost,192.168.0.111'
  • Edit /etc/postgresql/9.1/main/pg_hba.conf
host    all             all             IP/HERE                 trust

e.g.)

host    all             all             192.168.0.121/32        trust

The IP is not the local one but a remote one. e.g.) 192.168.0.121 means access from 192.168.0.121 is allowed.

  • Test if it works.
# -W: force password prompt
$ psql -h IP-TO-DB-SERVER -U USERNAME -W 

e.g.)

$ psql -h 192.168.0.123 -U db_user -W 

Roles (Users)

Setup Root User Password

  • Set up the password of the PostgreSQL root user
$ sudo -u postgres psql postgres 

Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password'; 

or

postgres=# \password postgres 

Use \quit or \q to quit psql.

postgres=# \q 


Create Role (User)

Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using CREATE ROLE[5] SQL command and the other one is using the programme, createuser[6].

CREATE ROLE

CREATE ROLE[7]

CREATE ROLE role_name; 
CREATE ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'password'; 
CREATE USER role_name WITH ENCRYPTED PASSWORD 'password'; 

(CREATE USER == CREATE ROLE + LOGIN)

Usually use this.

CREATE ROLE "role_name" WITH LOGIN ENCRYPTED PASSWORD 'password';
createuser
createuser [option...] [username] 

So to create a new user and a db belonging to him/her.

sudo -u postgres createuser -P username 
sudo -u postgres createdb -O username db_name 

-P: it will ask you to enter the password for the new user (role).


createuser accepts the following command-line arguments:

username

    Specifies the name of the PostgreSQL user to be created. This name must be different from all existing roles in this PostgreSQL installation. 
-s
--superuser

    The new user will be a superuser. 
-S
--no-superuser

    The new user will not be a superuser. This is the default. 
-d
--createdb

    The new user will be allowed to create databases. 
-D
--no-createdb

    The new user will not be allowed to create databases. This is the default. 
-r
--createrole

    The new user will be allowed to create new roles (that is, this user will have CREATEROLE privilege). 
-R
--no-createrole

    The new user will not be allowed to create new roles. This is the default. 
-l
--login

    The new user will be allowed to log in (that is, the user name can be used as the initial session user identifier). This is the default. 
-L
--no-login

    The new user will not be allowed to log in. (A role without login privilege is still useful as a means of managing database permissions.) 
-i
--inherit

    The new role will automatically inherit privileges of roles it is a member of. This is the default. 
-I
--no-inherit

    The new role will not automatically inherit privileges of roles it is a member of. 
-c number
--connection-limit number

    Set a maximum number of connections for the new user. The default is to set no limit. 
-P
--pwprompt

    If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication. 
-E
--encrypted

    Encrypts the user's password stored in the database. If not specified, the default password behavior is used. 
-N
--unencrypted

    Does not encrypt the user's password stored in the database. If not specified, the default password behavior is used. 
-e
--echo

    Echo the commands that createuser generates and sends to the server. 

You will be prompted for a name and other missing information if it is not specified on the command line.

createuser also accepts the following command-line arguments for connection parameters:

-h host
--host host

    Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. 
-p port
--port port

    Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. 
-U username
--username username

    User name to connect as (not the user name to create). 
-w
--no-password

    Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. 
-W
--password

    Force createuser to prompt for a password (for connecting to the server, not for the password of the new user).

    This option is never essential, since createuser will automatically prompt for a password if the server demands password authentication. However, createuser will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt. 


View Roles

  • To see all the exising roles.
SELECT * FROM pg_roles;
  • To see the details of an existing role
SELECT role_name FROM pg_roles; 


Change Role

  • ALTER ROLE[9] SQL
ALTER ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'password'; 


Remove Role

  • Using DROP ROLE[10] SQL
DROP ROLE role_name; 
dropuser name 

Databases

List Databases

postgres=# \l 

Select (Change) Database

postgres=# \c DB_NAME 

List Table

postgres=# \d 

Display Table Description

postgres=# \d TABLE_NAME 


Creating Database

  • Using CREATE DATABASE[12] SQL to create a new database.
CREATE DATABASE "db_name" 
CREATE DATABASE "db_name" OWNER "role_name" 
  • Similar to creating a role, it is also possible to create a new database from the shell by using createdb[13] command.
createdb [option...] [db_name] [description]
sudo -u postgres createuser -P username 
sudo -u postgres createdb -O username db_name 
-O owner
--owner owner
     Specifies the database user who will own the new database.

Change Database

e.g.)

postgres=# ALTER DATABASE db_name OWNER TO new_owner; 

Privileges

GRANT

e.g.)

postgres=# GRANT ALL PRIVILEGES ON DATABASE db_name TO username; 


Drop and Recreate database

  • Access db
$ sudo -u postgres psql postgres 

And drop and recreate the database.

DROP DATABASE DB_NAME; 
CREATE DATABASE DB_NAME OWNER ROLE_NAME; 

Back Up Database

$ sudo -u postgres pg_dump DB_NAME > DB_DUMP_DB_NAME.sql

With compression

pg_dump -Fc DB_NAME > "FILENAME"

e.g.)

$ pg_dump -Fc my_db > "my_db_backup.dump.compressed" 

Restore Database

$ sudo -u postgres psql --set ON_ERROR_STOP=on DB_NAME < DB_DUMP_DB_NAME.sql 
pg_restore --no-owner -U USER -h HOST -d DB_NAME FILENAME 

e.g.)

$ pg_restore --no-owner -U my_db_user -h HOST -d my_db "my_db_backup.dump.compressed" 


Run SQL Script

  • Use psql terminal and select DB.
$ sudo -u postgres psql 
postgres=# \c DB_NAME 

OR Access the DB directly

$ sudo -u postgres psql DB_NAME 
DB_NAME=# 
  • Run SQL script from within psql
DB_NAME=# \set ON_ERROR_STOP 
DB_NAME=# \i db.sql
  • Run SQL query
DB_NAME=# type SQL; 

e.g.)

DB_NAME=# SELECT * FROM some_table;

Store the Query Result in File

DB_NAME=# \o file_name 
# Run some query
DB_NAME=# SELECT * FROM some_table; 
# \o to finish
DB_NAME=# \o

Store Query Result in CSV file

\copy (SELECT * FROM products WHERE product_name LIKE 'SomePreFix%') To '/path/to/store/csv-file.csv' With CSV HEADER; 

Terminate any processing user of database

  • To terminate all the processing users of the database, run the following script.
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='DB_NAME';

Restart Server

  • To restart the server, /etc/init.d/postgresql-8.x (depending on the version).
sudo /etc/init.d/postgresql-8.4 restart

Install pgAdmin III

$ sudo apt-get install pgadmin3 

Troubleshooting

Shared Memory Segment Issue

References

  1. "PostgreSQL licence approved by OSI". Crynwr. 2010-02-18. http://www.crynwr.com/cgi-bin/ezmlm-cgi?17:mmp:969. Retrieved 2010-02-18. 
  2. "OSI PostgreSQL licence". Open Source Initiative. 2010-02-20. http://www.opensource.org/licenses/postgresql. Retrieved 2010-02-20. 
  3. "License". PostgreSQL Global Development Group. http://www.postgresql.org/about/licence. Retrieved 2010-09-20. 
  4. "PostgreSQL 8.4.4 Documentation". 2009. http://www.postgresql.org/docs/8.4/static. Retrieved 2010-08-02. 
  5. "PostgreSQL 8.4.4 Documentation - 20.1. Database Roles". 2009. http://www.postgresql.org/docs/8.4/static/database-roles.html. Retrieved 2010-08-02. 
  6. "PostgreSQL 8.4.4 Documentation - createuser". 2009. http://www.postgresql.org/docs/8.4/static/app-createuser.html. Retrieved 2010-08-02. 
  7. "PostgreSQL 8.4.4 Documentation - CREATE ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createrole.html. Retrieved 2010-08-02. 
  8. "PostgreSQL 8.4.4 Documentation - 20.1. Database Roles#Options". 2009. http://www.postgresql.org/docs/8.4/static/app-createuser.html#AEN67555. Retrieved 2010-08-02. 
  9. "PostgreSQL 8.4.4 Documentation - ALTER ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-alterrole.html. Retrieved 2010-08-02. 
  10. "PostgreSQL 8.4.4 Documentation - DROP ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-droprole.html. Retrieved 2010-08-02. 
  11. "PostgreSQL 8.4.4 Documentation - dropuser". 2009. http://www.postgresql.org/docs/8.4/static/app-dropuser.html. Retrieved 2010-08-02. 
  12. "PostgreSQL 8.4.4 Documentation - CREATE DATABASE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createdatabase.html. Retrieved 2010-08-02. 
  13. "PostgreSQL 8.4.4 Documentation - createdb". 2009. http://www.postgresql.org/docs/8.4/static/app-createdb.html. Retrieved 2010-08-02. 
Personal tools