PostgreSQL

From KevinWiki

Revision as of 15:43, 1 January 2016 (view source) Kevin (Talk | contribs) (→Back Up Database) ← Older edit		Revision as of 16:18, 1 January 2016 (view source) Kevin (Talk | contribs) (→Restore Database) Newer edit →
Line 357:		Line 357:
	</pre>		</pre>
		+	* Restore all databases backed up by pg_dumpall
		+	<pre>
		+	psql -f FILE postgres
		+	</pre>
		+
		+	e.g.)
		+	<pre>
		+	$ sudo -u postgres psql -f my_db_dump.sql postgres
		+	</pre>
	====Run SQL Script====		====Run SQL Script====

---

Revision as of 16:18, 1 January 2016

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 1 PostgreSQL 2 Installation 2.1 For Latest Version 3 Administration 3.1 Setup 3.1.1 Setup Authentication Methods 3.1.2 Access Remotely 3.2 Roles (Users) 3.2.1 Setup Root User Password 3.2.2 Create Role (User) 3.2.2.1 CREATE ROLE 3.2.2.2 createuser 3.2.3 View Roles 3.2.4 Change Role 3.2.5 Remove Role 3.3 Databases 3.3.1 List Databases 3.3.2 Select (Change) Database 3.3.3 List Table 3.3.4 Display Table Description 3.3.5 Creating Database 3.3.6 Change Database 3.3.7 Privileges 3.3.7.1 GRANT 3.3.8 Drop and Recreate database 3.3.9 Back Up Database 3.3.10 Restore Database 3.3.11 Run SQL Script 3.3.12 Store the Query Result in File 3.3.13 Store Query Result in CSV file 3.3.14 Terminate any processing user of database 3.4 Restart Server 4 Install pgAdmin III 5 Troubleshooting 5.1 Shared Memory Segment Issue 6 References

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 

• Reference: http://www.postgresql.org/download/linux/ubuntu/

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).

• Options^[8]

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; 

• Using dropuser^[11]

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

To custom-format archive file

pg_dump -Fc DB_NAME > "FILENAME"

e.g.)

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

• Backup the entire contents of DB cluster

pg_dumpall > FILE 

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" 

• Restore all databases backed up by pg_dumpall

psql -f FILE postgres

e.g.)

$ sudo -u postgres psql -f my_db_dump.sql postgres 

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

• http://askubuntu.com/questions/44373/how-to-fix-postgresql-installation
• http://leopard.in.ua/2013/09/05/postgresql-sessting-shared-memory

References