"

PostgreSQL

1 PostgreSQL

2 access over network

Postgres normally listens on port 5432.

On the server to *pg_hba.conf* file add line

host     all     all     84.50.108.130  255.255.255.255  md5

enlisting IP address of the location you want to connect from.

In the file *postgresql.conf* make sure the line

listen_addresses = ...

enlists server external network interface IP, or use

listen_addresses = '*'

as an alternative.

2.1 JDBC interoperability

base class org.postgresql.Driver
JDBC URL jdbc:postgresql://<host>:<port>/<schema>

Example URLs:

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database

3 data types

type occupied storage precision ranges
text variable unlimited length    
smallint 2 bytes small range fixed-precision -32768 to +32767
integer 4 bytes usual choice for fixed-precision -2147483648 to +2147483647
bigint 8 bytes large range fixed-precision -9223372036854775808 to 9223372036854775807
decimal variable user-specified precision, exact no limit
numeric variable user-specified precision, exact no limit
real 4 bytes variable-precision, inexact 6 decimal digits precision
double precision 8 bytes variable-precision inexact
serial 4 bytes autoincrementing integer 1 to 2147483647
bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

4 export / import

4.1 export all databases

pg_dumpall > postgres_dump.sql

4.2 single database

4.2.1 export

Command:

pg_dump

Commandline arguments:

–clean generate drop statements to clean database objects before recreating
–blobs include large objects in dump
–username=NAME connect as specified database user
–host=HOSTNAME database server host or socket directory
–port=PORT database server port number
–inserts dump data as INSERT commands, rather than COPY
–schema=SCHEMA dump the named schema(s) only
–schema-only dump only the schema, no data
<dbname> name of the database to connect to

Example:

  • export from local database
    pg_dump dbname > output_file
    
  • export from remote database
    pg_dump -h myHostName -p 5432 myDatabase > myDatabaseDumpFile.db
    
  • export with blobs
    pg_dump --clean --blobs --username myUserName --host myHostName --port 5433 myDatabase --inserts > /path/to/my/database/dump/file.sql
    

4.2.2 import

entire database from SQL file

  1. delete old schema
    DROP SCHEMA <schemaName> CASCADE;
    
  2. import updated version
    psql <database> < <dumpFile>
    

5 file / directory layout

default postgres home directory:

/var/lib/postgresql/

sample config files can be found in:

/usr/share/postgresql/

6 management

6.1 database

6.1.1 create database

from bash shell

createdb -U <user to connect as> -O <database owner> <database name>

from SQL prompt

create database <databaseName>;

6.1.2 erase database

dropdb <database name>

6.1.3 list available databases

psql -l

6.1.4 set database encoding

-E <encoding>

Example:

-E UTF-8

6.1.5 set database owner user

-O <user>
  • example

    Creates database named myDatabase with UTF-8 encoding and sets database owner to myUser.

    createdb -E UTF-8 -O myUser myDatabase
    

6.2 delete orphaned LOBs

vacuumlo -U <user> -h <host> -p <port> <database>

example:

vacuumlo -U <user> -h 127.0.0.1 -p 5433 <database>

6.3 log in as database administrator

sudo -u postgres psql

6.4 schema

6.4.1 create new schema

CREATE SCHEMA myschema;

Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is:

CREATE SCHEMA schemaname AUTHORIZATION username;

6.4.2 drop schema

To drop a schema if it's empty (all objects in it have been dropped), use:

DROP SCHEMA myschema;

To drop a schema including all contained objects, use:

DROP SCHEMA myschema CASCADE;

6.5 database process

6.5.1 start database server

method #1:

pg_ctl start

method #2:

/usr/bin/postgres -D /var/lib/pgsql/data

method #3:

/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

6.5.2 stop database

pg_ctl stop

6.6 user

6.6.1 list users

users are stored in table PG_SHADOW.

select * from pg_user;

6.6.2 create new database user

  1. as SQL command

    Create a user with no password

    CREATE USER myUser;
    

    Create a user with password

    CREATE USER myUser WITH PASSWORD 'myPassword'
    

    Create a user with a password, whose account is valid until 2022

    CREATE USER myUser WITH PASSWORD 'myPassword' VALID UNTIL 'Jan 1 2022'
    

    Create an account where the user can create databases

    CREATE USER myUser WITH PASSWORD myPassword CREATEDB
    
  2. at shell prompt

    login to system as postgres user execute command

    createuser <username>
    

6.6.3 change user password

ALTER USER <username> with encrypted password 'new_password';

6.6.4 default superuser

The default superuser is "postgres".

After postgres was installed on the system, default superuser password is not set.

To set default user password do:

  1. run psql as user "postgres"
    sudo -u postgres psql template1
    
  2. Set a password for the "postgres" database role using the command:
    \password postgres
    

6.7 permissions and security

GRANT ALL PRIVILEGES ON DATABASE "<database>" to <user>;
GRANT CONNECT ON DATABASE test to app;

6.7.1 Example pg_hba.conf entries

# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         127.0.0.1/32          trust

# The same as the last line but using a separate netmask column
#
# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         all         127.0.0.1     255.255.255.255     trust

# Allow any user from any host with IP address 192.168.93.x to connect
# to database "template1" as the same user name that ident reports for
# the connection (typically the Unix user name).
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    template1   all         192.168.93.0/24       ident sameuser

# Allow a user from host 192.168.12.10 to connect to database
# "template1" if the user's password is correctly supplied.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    template1   all         192.168.12.10/32      md5

# In the absence of preceding "host" lines, these two lines will
# reject all connection from 192.168.54.1 (since that entry will be
# matched first), but allow Kerberos 5 connections from anywhere else
# on the Internet.  The zero mask means that no bits of the host IP
# address are considered so it matches any host.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         192.168.54.1/32       reject
host    all         all         0.0.0.0/0             krb5

# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check.  If, for example, ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "omicron" that says "bryanh" is allowed to connect as "guest1".
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         192.168.0.0/16        ident omicron

# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their user name) except for administrators and
# members of group "support" who may connect to all databases.  The file
# $PGDATA/admins contains a list of user names.  Passwords are required in
# all cases.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   sameuser    all                               md5
local   all         @admins                           md5
local   all         +support                          md5

# The last two lines above can be combined into a single line:
local   all         @admins,+support                  md5

# The database column can also use lists and file names, but not groups:
local   db1,db2,@demodbs  all                         md5

7 software packages

pgaccess
GUI based database administration tool

7.1 psql

Intactive commandline utility for database interaction

7.1.1 connecting to database

psql -h <host> -p <port> -U <username> <database>

Example when connecting to local database:

psql <username> -h 127.0.0.1 -d <database>

7.1.2 commands

describe all tables \dt
describe given table \d <table>
exit psql \q
display available schemas \dn
display available databases \c
connect to database \c <databaseName>

8 tablespace

8.1 pg_default

tablespace is always located in the $PGDATA/base directory.

8.2 pg_global

tablespace stores cluster-wide tables like

  • pg_database,
  • pg_group,
  • pg_tablespace

you can't create objects in the pg_global tablespace.

9 temporary tables

TEMPORARY or TEMP tables

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.