Linux Tutorial: PostgreSQL Database and Linux

The PostgreSQL Database - Installation and Configuration:

Ubuntu: (16.04, 14.04) Install: sudo apt-get install postgresql

postgresql-9.X - libraries and SQL
postgresql-common - the database program
postgresql-client-9.X - utility programs and man pages
postgresql-client-common - utility programs and man pages
libpq5 - network client libraries

Starting the database: sudo service postgresql start

Red Hat Enterprise Linux 6 RPM packages:

postgresql-8.4.11-1.el6_2.x86_64 - commands, HTML docs and man pages
postgresql-server-8.4.11-1.el6_2.x86_64 - DB server and locale based messages
postgresql-libs-8.4.11-1.el6_2.x86_64 - libraries and locale based messages
postgresql-docs-8.4.11-1.el6_2.x86_64 - tutorials, examples and a monster PDF manual

Other RPM packages:

postgresql-test - lots of examples.
postgresql-jdbc - Java connectivity
postgresql-plperl - Perl connectivity
postgresql-plpython - Python connectivity
postgresql-devel - C language connectivity

Starting the database (as root): service postgresql start

The first time this is run you will get the following error:
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first. [FAILED]

To initialize the system for the first run (as root): service postgresql initdb
Initializing database: [ OK ]

Once the database initialization has occurred, one can then start the database (as root): service postgresql restart

Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

The user "postgres" should have already been configured by the installation of the RPMs. Info:

User: postgres
Home directory: /var/lib/pgsql
Default shell: /bin/bash

A password will be missing. As root issue the command: passwd postgres to assign a password for user postgres.

PostgreSQL post install:

PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA

Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database.
Starting the database server: As root. (from most to least favorite method)
service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)
OR
/etc/rc.d/init.d/postgresql start
(If the database has not already been initialized with initdb, this will be performed by the script)
OR
/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
OR
/usr/bin/postmaster -D /var/lib/pgsql/data &

Notes:
- Configuration file: /var/lib/pgsql/data/postgresql.conf
  By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here.
- Host Access file: /var/lib/pgsql/data/pg_hba.conf
- Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf

Using the PostgreSQL Database:

Create a database: /usr/bin/createdb bedrock
(As Linux user postgres: sudo su - postgres)

Connect to the database: /usr/bin/psql bedrock
Execute command as Linux user postgres
You will now be at the PostgreSQL command line prompt.

[prompt]$ psql             - or "psql bedrock"
Welcome to psql, the PostgreSQL interactive terminal.

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

bedrock=#
bedrock=# \c bedrock
You are now connected to database bedrock.
bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
CREATE TABLE
bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT 0 1
bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
bedrock=# INSERT into employee VALUES ('Barney Rubble','Sales','Neighbor');
bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
bedrock=# SELECT * from employee;
         name         |         dept         |       jobtitle       
----------------------+----------------------+----------------------
 Fred Flinstone       | Quarry Worker        | Rock Digger         
 Wilma Flinstone      | Finance              | Analyst             
 Barney Rubble        | Sales                | Neighbor            
 Betty Rubble         | IT                   | Neighbor            
(4 rows)

bedrock=#
bedrock=# \q

Database discovery / Examine a database (as user postgres: su - postgres):
[postgres]$ psql
- \l :List databases
- \c database-name :Connect to database
- \c :Show the database your are connected to
- \d :List tables in database
- \d table-name :Describe table
- SELECT * FROM table-name :List table contents
More info:
- Create a user:
  - Command line: [prompt]$ createuser dude
  - SQL: CREATE USER dude WITH PASSWORD 'supersecret';
    Change with ALTER USER
- Grant priveliges:
  - SQL: GRANT UPDATE ON table-name to dude
  - SQL: GRANT SELECT ON table-name to dude
  - SQL: GRANT INSERT ON table-name to dude
  - SQL: GRANT DELETE ON table-name to dude
  - SQL: GRANT RULE ON table-name to dude
  - SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
- Delete a user:
  - Command line: [prompt]$ dropuser SuperDude
- Delete a database:
  - Command line:
    - [prompt]$ destroydb bedrock
    - [prompt]$ dropdb bedrock
  - SQL: DROP DATABASE bedrock;
- Create a database:
  - Command line: [prompt]$ createdb bedrock -U user-name -W
    You will be prompted for a password. (or execute as Linux user postgres without -U and -W options)
  - SQL: CREATE DATABASE bedrock
- Version Upgrades:
  - Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
  - Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
    The table template1 is the default administrative database.

postgres=# CREATE DATABASE bedrock;
CREATE DATABASE
postgres=# \c bedrock
You are now connected to database "bedrock" as user "postgres".
bedrock=# CREATE USER dude WITH PASSWORD 'supersecret';
CREATE ROLE
bedrock=# GRANT ALL PRIVILEGES ON employee to dude;
GRANT
bedrock=# \z
                                  Access privileges
 Schema |   Name   | Type  |     Access privileges     | Column privileges | Policies 
--------+----------+-------+---------------------------+-------------------+----------
 public | employee | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | dude=arwdDxt/postgres     |                   | 
(1 row)

User GUI interfaces to the database are often written with web based frameworks like PHP or Java with a servlet back-end. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example.

Links/Info:

PostgreSQL additional info and documentation:
- /usr/share/doc/postgresql/
- /usr/share/doc/postgresql-common/
Resources: /usr/share/pgsql/

Backup / Restore a PostgreSQL Database:

Backup / Dump a database:

[prompt]$ pg_dump dbname > outfile
[prompt]$ pg_dump dbname -h localhost -U postgres -t tablename > outfile
if migrating the resulting SQL into another SQL database, add the option: --no-owner. This eliminates the ALTER OWNER or SET SESSION AUTHORIZATION statements.
[prompt]$ pg_dumpall > outfile
[prompt]$ pg_dumpall -Fc dbname > outfile

Restore a database:

[prompt]$ psql dbname < infile
where infile is the file output by the pg_dump command. The database dbname will not be created by this command but can be generated with: createdb dbname
[prompt]$ psql -f infile postgres
if you are loading into an empty cluster then "postgres" should usually be used.

PostgreSQL GUI Admin Tools:

pgadmin:

PgAdmin is a GUI PostgreSQL administration tool which allows one to monitor database server activity, behavior, health as well as administer users, schemas and data. Advanced functions such as procedural language debugging, backups and permissions management make PgAdmin indespensible.

Install:

Ubuntu: sudo apt install pgadmin3

Note that pgadmin3 does not allow null passwords while psql will, thus the default no-password for user "postgres" will not work. Assign a password using psql with the database command: ALTER USER postgres WITH PASSWORD 'supersecret';

Run:

Allow X-Windows session to display session generated by another user (postgres): xhost +
Switch user to postgres: sudo su - postgres
pgadmin3
File + Add Server:
Traverse the "bedrock" database tree to show the table "employee":
With the "employee" table highlighted in the tree, select the grid icon "View the data in the selected object":

Home Page: PgAdmin.org - documentation

PostgreSQL Man Pages:

Linux shell commands:

createdb - Create a new PostgreSQL database
createlang - Add a new programming language to a PostgreSQL database
createuser - Create a new PostgreSQL user
dropdb - Remove an existing PostgreSQL database
droplang - Remove a programming language from a PostgreSQL database
dropuser - Drops (removes) a PostgreSQL user
pg_dump - Extract a PostgreSQL database into a script file. Example: pg_dump dbname > outfile
pg_dumpall - Extract all PostgreSQL databases into a script file
vacuumdb - Clean and analyze a PostgreSQL database
psql - PostgreSQL interactive terminal

PostgreSQL SQL Statement Man Pages:

abort - abort the current transaction
alter group - change role name or membership
alter table - change the definition of a table
alter user - change a database role
begin - start a transaction block
checkpoint - force a transaction log checkpoint
close - close a cursor
cluster - cluster a table according to an index
comment - define or change the comment of an object
commit - commit the current transaction
copy - copy data between a file and a table
create aggregate - define a new aggregate function
create constraint trigger - define a new constraint trigger
create database - create a new database
create function - define a new function
create group - define a new database role
create index - define a new index
create language - define a new procedural language
create operator - define a new operator
create rule - define a new rewrite rule
create sequence - define a new sequence generator
create table - define a new table
create table as - define a new table from the results of a query
create trigger - define a new trigger
create type - define a new data type
create user - define a new database role
create view - define a new view
declare - define a cursor
delete - delete rows of a table
drop aggregate - remove an aggregate function
drop database - remove a database
drop function - remove a function
drop group - remove a database role
drop index - remove an index
drop language - remove a procedural language
drop operator - remove an operator
drop rule - remove a rewrite rule
drop sequence - remove a sequence
drop table - remove a table
drop trigger - remove a trigger
drop type - remove a data type
drop user - remove a database role
drop view - remove a view
end - commit the current transaction
explain - show the execution plan of a statement
fetch - retrieve rows from a query using a cursor
grant - define access privileges
insert - create new rows in a table
listen - listen for a notification
load - load or reload a shared library file
lock - Unimplemented system call
move - move curses window cursor
notify - generate a notification
reindex - rebuild indexes
reset - restore the value of a run-time parameter to the default value
revoke - remove access privileges
rollback - abort the current transaction
select - retrieve rows from a table or view
select into - define a new table from the results of a query
set - change a run-time parameter
set constraints - set constraint checking modes for the current transaction
set transaction - set the characteristics of the current transaction
show - show the value of a run-time parameter
truncate - empty a table or set of tables
unlisten - stop listening for a notification
update - update rows of a table
vacuum - garbage-collect and optionally analyze a database

Links:

Postgresql.org
pgAdmin.org - GUI admin tool
Devart: PostgreSQL ODBC Driver - commercial ODBC Driver for Linux

Books:

	PostgreSQL by Korry Douglas, Susan Douglas ISBN #0735712573, New Riders
	PostgreSQL Essential Reference by Barry Stinson ISBN #0735711216, New Riders
	PostgreSQL: Developer's Handbook by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig ISBN #0672322609, SAMS
	Practical PostgreSQL John C. Worsley, Joshua D. Drake ISBN #1565928466, O'Reilly
	Beginning Databases with PostgreSQL by Richard Stones, Neil Matthew ISBN #1861005156, Wrox Press Inc

YoLinux Tutorial: The PostgreSQL Database and Linux

Backup / Dump a database:

Restore a database:

pgadmin:

Linux shell commands:

PostgreSQL SQL Statement Man Pages: