PostgreSQL Server Administration

 

Login to the PostgreSQL Server

Default PostgreSQL Superuser "postgres"

The PostgreSQL installation creates a "UNIX USER" called postgres, who is ALSO the "Default PostgreSQL's SUPERUSER". The UNIX USER postgres cannot login interactively to the system, as its password is not enabled.

Login as PostgreSQL Superuser "postgres" via "psql" Client

To run psql using "UNIX USER" postgres, you need to invoke "sudo -u postgres psql", which switches to "UNIX USER" postgres and run the psql command.

$ sudo -u postgres psql
   -- Run command "psql" as UNIX USER "postgres".
   -- Enter the CURRENT SUPERUSER password for sudo.
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# help 
You are using psql, the command-line interface to PostgreSQL.
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

-- Display version
postgres=# SELECT version();
     version                                                   
---------------------------------------------------
 PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) ...
press q to quit display

-- Quit
postgres=# \q

Notes:

• The PostgreSQL's prompt is in the format of databaseName=# for superuser or databaseName=> for regular user. In this case, the current database is also called postgres, which is the same as the username.
• The prompt changes to -# (superuser) or -> (regular user) for command continuation.
• Type "help" to see the help menu.
• Type \q to quit.
• Type \? to see all the psql commands.
• You need to end a SQL statement with a semicolon (;) or \g (for GO) to execute the statement. For example, in "SELECT version();".
• Comments: In a SQL script or command, an end-of-line comment begins with --; a block comment is enclosed by /* and */

3.3  Create Database, Create Table, CURD (Create-Update-Read-Delete) Records

-- Login to server
$ sudo -u postgres psql
......

-- List all databases via \l (or \list), or \l+ for more details
postgres=# \l
   Name    | ...
-----------+-----------
postgres   | ...
template0  | ...
template1  | ...
 
-- Help on SQL command syntax
postgres=# \h CREATE DATABASE
......
 
-- Create a new database called mytest
postgres=# CREATE DATABASE mytest;
CREATE DATABASE
   -- By default, the owner of the database is the current login user.
 
-- List all databases via \l (or \list), or \l+ for more details
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mytest    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ......

   -- By default, CREATE DATABASE copies the "template1", which you could customize.
   -- "template0" has the same contents as "template1", but you should not change.
 
-- Connect to mytest database via \c (or \connect)
-- Take note of the change of database name in the command prompt.
postgres=# \c mytest 
You are now connected to database "mytest" as user "postgres".

-- Display all tables (aka relations) via \dt or \dt+ for more details
mytest=# \dt
Did not find any relations.
 
-- We will be using an enumeration (TYPE) in our table, let's define it.
mytest=# CREATE TYPE cat_enum AS ENUM ('coffee', 'tea');
CREATE TYPE

-- Display all types via /dT or /dT+ for more details.
mytest=# \dT+
                        List of data types
 Schema |   Name   | Internal name | Size | Elements | Description 
--------+----------+---------------+------+----------+-------------
 public | cat_enum | cat_enum      | 4    | coffee  +| 
        |          |               |      | tea      | 
 
-- Create a new table.
mytest=# CREATE TABLE IF NOT EXISTS cafe (
  id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  category cat_enum NOT NULL,   -- Use the enum type defined earlier
  name VARCHAR(50) NOT NULL,    -- Variable-length string of up to 50 characters
  price NUMERIC(5,2) NOT NULL,  -- 5 digits total, with 2 decimal places
  last_update DATE              -- 'YYYY-MM-DD'
);
NOTICE:  CREATE TABLE will create implicit sequence "cafe_id_seq" for serial column "cafe.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cafe_pkey" for table "cafe"
CREATE TABLE
 
-- Display all tables in the current database, via \dt or \dt+ for more details
mytest=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description 
--------+------+-------+----------+---------+-------------
 public | cafe | table | postgres | 0 bytes | 

-- Display details of a particular table.
mytest=# \d+ cafe
                                               Table "public.cafe"
   Column    |         Type          |                     Modifiers                     | Storage  | Description 
-------------+-----------------------+---------------------------------------------------+----------+-------------
 id          | integer               | not null default nextval('cafe_id_seq'::regclass) | plain    | 
 category    | cat_enum              | not null                                          | plain    | 
 name        | character varying(50) | not null                                          | extended | 
 price       | numeric(5,2)          | not null                                          | main     | 
 last_update | date                  |                                                   | plain    | 
Indexes:
    "cafe_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

-- Insert rows.
mytest=# INSERT INTO cafe (category, name, price) VALUES
  ('coffee', 'Espresso', 3.19),
  ('coffee', 'Cappuccino', 3.29),
  ('coffee', 'Caffe Latte', 3.39),
  ('coffee', 'Caffe Mocha', 3.49),
  ('coffee', 'Brewed Coffee', 3.59),
  ('tea', 'Green Tea', 2.99),
  ('tea', 'Wulong Tea', 2.89);
INSERT 0 7
-- The output shows the OID (Object Identification Number) and the rows affected.
-- Each row has an hidden OID. OID is not a standard SQL feature and not recommended.
   
-- Query (SELECT)
mytest=# SELECT * FROM cafe;
 id | category |     name      | price | last_update 
----+----------+---------------+-------+-------------
  1 | coffee   | Espresso      |  3.19 | 2013-12-14
  2 | coffee   | Cappuccino    |  3.29 | 2013-12-14
  3 | coffee   | Caffe Latte   |  3.39 | 2013-12-14
  4 | coffee   | Caffe Mocha   |  3.49 | 2013-12-14
  5 | coffee   | Brewed Coffee |  3.59 | 2013-12-14
  6 | tea      | Green Tea     |  2.99 | 2013-12-14
  7 | tea      | Wulong Tea    |  2.89 | 2013-12-14
(7 rows)

mytest=# SELECT name, price FROM cafe WHERE category = 'coffee' AND price < 3.3; 
    name    | price 
------------+-------
 Espresso   |  3.19
 Cappuccino |  3.29
(2 rows)

-- Update
mytest=# UPDATE cafe SET price = price * 1.1 WHERE category = 'tea';
UPDATE 2
 
mytest=# SELECT * FROM cafe WHERE category = 'tea';
 id | category |    name    | price | last_update 
----+----------+------------+-------+-------------
  6 | tea      | Green Tea  |  3.29 | 2013-12-14
  7 | tea      | Wulong Tea |  3.18 | 2013-12-14
(2 rows)

-- Delete
mytest=# DELETE FROM cafe WHERE id = 6;
DELETE 1
 
mytest=# SELECT * FROM cafe WHERE category = 'tea';
 id | category |    name    | price | last_update 
----+----------+------------+-------+-------------
  7 | tea      | Wulong Tea |  3.18 | 2013-12-14
(1 row)

-- Quit
mytest=# \q

Notes:

• Take note that there are two sets of commands:
  • psql commands: such as \c, \l, \dt.
  • SQL commands: such as CREATE DATABASE, CREATE TABLE and SELECT. SQL command KEYWORDS are not case sensitive. I show them in uppercase for clarity. PostgreSQL automatically converts all names (identifiers) to lowercase (even you type in uppercase or mixed case). To use uppercase or mixed case, you need to double-quote the names.
• You need to end your SQL commands with a semi-colon (;) or \g. If you forget to enter a semi-colon, the command-prompt changes to "dbname-#" to indicate continuation (A SQL command can span many lines). You can enter the semi-colon on the new line.

More on psql commands

• \?: show all psql commands.
• \h sql-command: show syntax on SQL command.
• \c dbname [username]: Connect to database, with an optional username (or \connect).
• Display Commands: You can append + to show more details.
  • \l: List all database (or \list).
  • \d: Display all tables, indexes, views, and sequences.
  • \dt: Display all tables.
  • \di: Display all indexes.
  • \dv: Display all views.
  • \ds: Display all sequences.
  • \dT: Display all types.
  • \dS: Display all system tables.
  • \du: Display all users.
• \x auto|on|off: Toggle|On|Off expanded output mode.

More on SQL commands

See ....

3.4  Using Utility "createuser" and "createdb"

As an example, let's create a PostgreSQL user called testuser, and a new database called testdb owned by testuser.

# Create a new PostgreSQL user called testuser, allow user to login, but NOT creating databases
$ sudo -u postgres createuser --login --pwprompt testuser
Enter password for new role: xxxx

# Create a new database called testdb, owned by testuser.
$ sudo -u postgres createdb --owner=testuser testdb

Tailor the PostgreSQL configuration file /etc/postgresql/10/main/pg_hba.conf to allow non-default user testuser to login to PostgreSQL server, by adding the following entry:

# TYPE  DATABASE    USER        ADDRESS          METHOD
local   testdb      testuser                     md5

Restart PostgreSQL server:

$ sudo service postgresql restart

Login to PostgreSQL server:

# Login to PostgreSQL: psql -U user database
$ psql -U testuser testdb
Password for user testuser: ......
psql (9.3.10)
Type "help" for help.

# List all databases (\list or \l)
testdb=>\list
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | testuser | UTF8     | en_SG.UTF-8 | en_SG.UTF-8 |
......

# Create table
testdb=> create table if not exists cafe (
           name varchar(50),
           price numeric(5,2),
           primary key (name));
CREATE TABLE

# Insert rows
testdb=> insert into cafe values
           ('Espresso', 3.99),
           ('Green Tea', 2.99);
INSERT 0 2

# Select query
testdb=> select * from cafe;
   name    | price 
-----------+-------
 Espresso  |  3.99
 Green Tea |  2.99
(2 rows)

# To change password for the current user
testdb=> \password
......

testdb=> \q

You can also use other utilities like dropuser, dropdb, and etc.

3.5  Commonly-used SQL Data Types

As illustrated in the above example, the commonly-used SQL data types in PostgreSQL are:

1. INT, SMALLINT: whole number. There is no UNSIGNED attribute in PostgreSQL.
2. SERIAL: auto-increment integer (AUTO_INCREMENT in MySQL).
3. REAL, DOUBLE: single and double precision floating-point number.
4. CHAR(n) and VARCHAR(n): fixed-length string of n characters and variable-length string of up to n characters. String literals are enclosed by single quotes, e.g., 'Peter', 'Hello, world'.
5. NUMERIC(m,n): decimal number with m total digits and n decimal places (DECIMAL(m,n) in MySQL).
6. DATE, TIME, TIMESTAMP, INTERVAL: date and time.
7. User-defined types.
8. NULL: A special value indicates unknown value or no value (of an optional field), which is different from 0 and empty string (that represent known value of 0 and empty string). To test for NULL value, use operator IS NULL or IS NOT NULL (e.g., email IS NULL). Comparing two NULLs with = or != results in unknown.

4.  PostgreSQL Administration

4.1  Default Superuser "postgres"

During installation, a "UNIX USER" (who cannot login to the system interactively) called postgres is created. You can verify by checking the entry in /etc/passwd and /etc/shadow:

$ sudo less /etc/passwd | grep postgres
postgres:x:120:130:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
 
$ sudo less /etc/shadow | grep postgres
postgres:*:16049:0:99999:7:::  
   // * indicates that password is not enabled (this user cannot login interactively).

The user postgres belongs to the following groups:

$ groups postgres
postgres : postgres ssl-cert

An entry is also defined in PostgreSQL's HBA (Host-Based Authentication) configuration file for this user (PostgreSQL 9.5, NOT 10?!):

$ sudo less /etc/postgresql/10/main/pg_hba.conf
......
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
   // UNIX user "postgres" can access all databases from the localhost.
   // The "peer" authentication method means that if "foo" is a UNIX user and "foo" is also an PostgreSQL user,
   // then "foo" can login to PostgreSQL server from localhost without password.
 
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

4.2  Authentication Methods

PostgreSQL supports a number of authentication methods. The commonly-used are:

• ident, peer: identical (or fully match) to the OS account, with an optional name mapping defined in pg_ident.conf file. ident is applicable to TCP/IP; while peer for "local" connection.
• md5: require md5-hashed password (most common).
• password: require clear-text password.
• trust: no password, as long as meeting the IP, user, and database criteria defined in the HBA.
• reject: reject login immediately.
• others, such as GSSAPI, SSPI, Kerberos, LDAP, RADIUS, Certificate, PAM.

There are two ways to login PostgreSQL:

1. By running the "psql" command as a UNIX user which is also configured as PostgreSQL user using so-called IDENT/PEER authentication, e.g., "sudo -u postgres psql".
2. Via TCP/IP connection using PostgreSQL's own managed username/password (using so-called MD5 authentication).

4.3  Set a Password for User postgres

To set a password, login to PostgreSQL server using postgres via psql and issue command "\password username", as follows:

-- Login in to server via "psql" with user "postgres"
$ sudo -u postgres psql
......
 
-- Change password for current user "postgres"
postgres=# \password postgres
Enter new password: xxxx
Enter it again: xxxx
  
-- Display the user table
postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |          |
 
-- Quit
postgres=# \q

To test the password login, you need to change the the authentication method from "peer" to "md5" in pg_hba.conf. Restart the server, and login via sudo -u postgres sql. The system will prompt you for the password.

4.4  Add your UNIX user as PostgreSQL user

Using user "postgres" (which is a UNIX user as well as PostgreSQL user) to run "psql" involves switching user (via "sudo -u username" or "su - username"). You can simply the process by configuring your current UNIX userID as PostgreSQL user, as follows:

-- Switch to default superuser "postgres",
-- run utility "createuser" to create a superuser same name as current login.
-- "$USER" is an environment variable denoting the current login user.
$ sudo -u postgres createuser --superuser $USER

-- Create the default database which shall be the same as the username.
$ sudo -u postgres createdb $USER

-- Now, you can invoke "psql" from your user account.
$ psql
......
yourusername=# SELECT * FROM pg_user;
   usename    | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
--------------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 yourusername |    16424 | t           | t        | t         | t       | ******** |          |            
 
-- Perform database operations.
.......

4.5  Create Group and User

The latest PostgreSQL treats both group and user as role. Some roles can login (i.e. user), some roles have member of other roles (i.e., group). You should use CREATE ROLE to create both users and groups (CREATE USER and CREATE GROUP are meant for compatibility).

-- Create a login user role
CREATE ROLE user1 LOGIN PASSWORD 'xxxx' CREATEDB VALID UNTIL 'infinity';

-- Create a login superuser role
CREATE ROLE user2 LOGIN PASSWORD 'xxxx' SUPERUSER VALID UNTIL '2019-12-31';

-- Create a group role
CREATE ROLE group1 INHERIT;
-- Add a user (or group) to this group
GRANT group1 TO user1;

4.6  Backup and Restore

PostgreSQL provides two utilities for backup:

1. pg_dump: backup a specific database.
2. pg_dumpall: backup all databases and server globals. Need to be run by superuser.

For example,

-- Create a compressed backup for a database
pg_dump -h localhost -p 5432 -U username -F c -b -v -f mydatabase.backup mydatabase

-- Create a plain-text backup for a database, including the CREATE DATABASE
pg_dump -h localhost -p 5432 -U username -C -F p -b -v -f mydatabase.backup.sql mydatabase

To restore a database from backup generated by pg_dump or pg_dumpall:

1. Use "psql" to run the plain-text backup.

   -- Run SQL script
   $ psql -U username -f filename.sql

2. Use utility "pg_restore" to restore compressed backup.