PostgreSQL

PostgreSQL
[http://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F What is PostgreSQL? How is it pronounced? What is Postgres?]

PostgreSQL is pronounced Post-Gres-Q-L. (For those curious about how to say "PostgreSQL", an audio file is available.)

PostgreSQL is an object-relational database system that has the features of traditional proprietary database systems with enhancements to be found in next-generation DBMS systems. PostgreSQL is free and the complete source code is available.

PostgreSQL development is performed by a team of mostly volunteer developers spread throughout the world and communicating via the Internet. It is a community project and is not controlled by any company. To get involved, see the Developer FAQ.

Postgres is a widely-used nickname for PostgreSQL. It was the original name of the project at Berkeley and is strongly preferred over other nicknames. If you find 'PostgreSQL' hard to pronounce, call it 'Postgres' instead.

Pronunciation
[http://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F What is PostgreSQL? How is it pronounced? What is Postgres?]

PostgreSQL is pronounced Post-Gres-Q-L. (For those curious about how to say "PostgreSQL", an audio file is available.)

Alternative pronunciation:

[03:35pm|peerce> I'm of the post-gres-sequel camp, myself :D [03:35pm|peerce> or pee-gee-sequel

Documentation
PostgreSQL Documentation - http://www.postgresql.org/docs/

CLI Commands
Note: If you do not specify a database name, the name of the current user will be used as the database name.

Create User: su - postgres createuser -U postgres -dAP [NEWUSER] \q
 * 1) On most systems, to create the user in PostgreSQL, you will need to login as the root user, and then
 * 1) As the postgres user, you then need to create a new user:

You will need to create a database for the user or receive this error: (NOTE: the db can be created by the user) psql: FATAL: database "[USER]" does not exist

Create Database: createdb

Delete Database: dropdb

PostgreSQL interactive terminal program: psql

Execute SQL from file: psql --username postgres --username postgres --dbname dbdocsds -f mysql.sql

Execute SQL from command line: psql -c "SELECT ..."

Create database that doesn't exist (connect to 'postgres' maintenance table) psql -U user1 -c "create database user1;" postgres

Create User
/opt/postgresql/bin/createuser --username postgres [NEWUSERNAME] Note: remember to give the user permission to create databases, or you will have to manually assign the user to a database later.

/opt/postgresql/bin/createuser --username postgres [NEWUSERNAME] --no-superuser --createdb --no-createrole

Change Password
/opt/postgresql/bin/psql --username postgres alter role [NEWUSERNAME] password '[PASSWORD]';

Create Database
/opt/postgresql/bin/createdb --username [NEWUSERNAME] [NEWDATABASE]

Note: give the user the ability to create a database.

To change the owner of an existing database:

/opt/postgresql/bin/psql --username postgres ALTER DATABASE [DATABASE] OWNER TO [NEWUSERNAME]

Connect to Database
/opt/postgresql/bin/psql --username [NEWUSERNAME] [NEWDATABASE]

Backup Database
/opt/postgresql/bin/pg_dump --host localhost --port 5432 --username postgres --format custom --blobs --verbose --file database-backup.sql dbdocsds /opt/postgresql/bin/pg_dump --username postgres --format custom --blobs --verbose --file database-backup.sql dbdocsds

Note: '--format custom' is the most flexible format, and allows for data to be reordered.

Pass in password with: PGPASSWORD=... /opt/postgresql/bin/pg_dump ...

Restore Database
/opt/postgresql/bin/pg_restore --host localhost --port 5432 --username postgres --dbname dbdocsds --clean --verbose database-backup.sql /opt/postgresql/bin/pg_restore --username [NEWDATABASE] --dbname [NEWDATABASE] --no-owner --verbose database-backup.sql

Options: --no-owner # skip restoration of object ownership --clean    # clean (drop) database objects before recreating

connection
PostgreSQL interactive terminal program: psql psql -U -W

mydb=>
 * 1) Regular user:

mydb=#
 * 1) Super user:

\q
 * 1) To quit:

Note: Being a superuser means that you are not subject to access controls.

Show version: select version;

databases
List all databases: select datname from pg_database; \l

The currently connected database is shown in the prompt: mydb=>

Show current database: select current_database;

Connect to alternate database: \connect [database] \c [database]

Create Database: $ su - postgres $ createdb [database]

tables
List tables: \dt \d select tablename from pg_tables where tableowner = '[user]';

Psql - show all tables (including system tables) select * from pg_tables;

Describe table: (including columns and indexes) \d [tablename] \d+ [tablename] # with extra info

Create table: CREATE TABLE  (    (),  --     ... );

Drop table: DROP TABLE ;

List foreign keys: SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.contype = 'f' ORDER BY 1;

users
PostgreSQL actually does privilege management in terms of "roles". The concept of roles subsumes the concepts of "users" and "groups". In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both. 

See PostgreSQL: Documentation: Manuals: PostgreSQL 9.1: Database Roles - http://www.postgresql.org/docs/9.1/static/user-manag.html

NOTE: A "user" is nothing more than a "role" with "login" privileges.

Primary roles: LOGIN | NOLOGIN SUPERUSER | NOSUPERUSER | CREATEUSER | NOCREATEUSER CREATEDB | NOCREATEDB CREATEROLE | NOCREATEROLE

Roles can be also be groups and other roles can be assigned to other roles.

---

Show all users: (role with login privilege) select * from pg_user;

Show all roles: (and groups) \du select * from pg_roles;

Show groups of a user: select rolname from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.member) join pg_roles on (pg_roles.oid=pg_auth_members.roleid) where pg_user.usename='[USER]';

Show current user: select user; select current_user; # identical to 'select user'

Create user: (given login privilege) CREATE USER ; CREATE ROLE LOGIN;

Create user from command line: $ su - postgres $ createuser [user]

Create role: (make sure to give login privilege, or won't be a usable user) CREATE ROLE LOGIN CREATEDB;

Alter user: ALTER ROLE ; ALTER ROLE LOGIN CREATEDB;

Rename role: ALTER ROLE [name] RENAME TO [newname];

Change password: ALTER ROLE PASSWORD ' ';

Drop user: DROP DATABASE ; DROP ROLE ;
 * 1) first drop any dependent databases:

Drop user from command line: dropuser [user]

permissions
It appears that by default another user can:
 * connect to my database
 * list my tables
 * create a table (and then I don't have permissions to use a table in my database!)

Error when user1 tries to view user2's table contents: user2=> select * from u2; ERROR: permission denied for relation u2
 * 1) psql -U user1 user2

---

Grant role permissions on a table: GRANT [SELECT,INSERT,UPDATE,DELETE,TRUNCATE,ALL PRIVILEGES] ON [TABLE] TO [ROLE];

Create database for another user: CREATE DATABASE [db]; GRANT ALL PRIVILEGES ON DATABASE [db] TO [user];

Show grants for current database: \z

Revoke grant: REVOKE ALL PRIVILEGES ON kinds FROM manuel; REVOKE admins FROM joe;

---

Grant privilege on all tables trick: psql -d your_database \t \a \o /tmp/sqlscript SELECT 'GRANT SELECT ON ' || schemaname || '.' || tablename || ' TO PUBLIC ;' FROM pg_tables WHERE tableowner = CURRENT_USER; \o \i /tmp/sqlscript

one for 9.x: GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx;

maybe this: grant select on db_name.table_name to read_only_user;

another: SELECT 'GRANT SELECT ON ' || c.relname || ' TO foobar;' FROM pg_class AS c LEFT OUTER JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN('pg_catalog', 'pg_toast') AND pg_table_is_visible(c.oid);

From shell: for table in `echo "SELECT relname FROM pg_stat_all_tables;" | psql database | grep -v "pg_" | grep "^ "`; do echo "GRANT SELECT ON TABLE $table to my_new_user;" echo "GRANT SELECT ON TABLE $table to my_new_user;" | psql database done

SQL
Adjust output: \t [on|off] # Don't list column headers \x [on|off] # expanded output (similar to \G in mysql) \a          # toggle aligned output (not tabbed) \H          # toggle HTML output \f '[string]' # field separator for unaligned output (default '|'), for tab separated use '\t'

\w [file]   # write query to file (not query output) \p          # show query buffer \r          # reset/clear query buffer \s [file]   # show query history (or write to file)

\g [file]   # execute last query and send output to file \o [file]   # send all query results to file \o          # turn off sending query results to file

Find distinct (unique) values from table: SELECT DISTINCT a, b FROM tab; SELECT DISTINCT ON (a) a, b FROM tab;

Select limited number of rows: SELECT * FROM tab LIMIT 10;

Select true/false from boolean: SELECT * FROM tab WHERE col = t; SELECT * FROM tab WHERE col = 't'; SELECT * FROM tab WHERE col = true; SELECT * FROM tab WHERE col is true; SELECT * FROM tab WHERE col is not false;

Select null: SELECT * FROM tab WHERE col is null; SELECT * FROM tab WHERE col is not null;

Count records: SELECT count(*) FROM tab;

Inner join: SELECT table1.col, table2.col FROM table1, table2 WHERE table1.id = table2.id; SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name); # alternate form

Misc Functions: SELECT current_database; SELECT current_user; SELECT user; SELECT version;

Tutorials

 * PostgreSQL 8.3.0 Documentation - Tutorial
 * YoLinux Tutorial: The PostgreSQL Database and Linux

Installation
Install PostgreSQL: yum install postgresql postgresql-server

Start PostgreSQL server: (and initalize database too) service postgresql start

Databases are created here: /var/lib/pgsql/data

Create root db user (optional): su - postgres psql CREATE ROLE root LOGIN SUPERUSER; \q
 * 1) login as postgresql user:
 * 2) possibly: su - postgresql

---

If you get this error, either create a 'root' database, or specify correct database 'psql mydb' psql: FATAL: database "root" does not exist
 * 1) psql

If you get this error, create the root db user. psql: FATAL: role "root" does not exist
 * 1) psql

---

One option is to create a super-user with something like: (not root)

jeoff$ su - postgres postgres$ createuser -d -a -P jeoff

jeoff$ createdb ibmadb ---

PostgreSQL: Linux packages - CentOS, Fedora and RHEL Yum Repository - http://www.postgresql.org/download/linux#yum

Exclude distro's postgresql: exclude=postgresql*
 * On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections
 * On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.
 * On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.

wget http://yum.pgrpms.org/reporpms/9.0/pgdg-redhat-9.0-2.noarch.rpm rpm -Uvh pgdg-redhat-9.0-2.noarch.rpm

This will create the following repo: /etc/yum.repos.d/pgdg-90-redhat.repo

Install postgresql: yum install postgresql90 postgresql90-server

Disable repo after install: mv /etc/yum.repos.d/pgdg-90-redhat.repo /etc/yum.repos.d/pgdg-90-redhat.repo_disabled

Initialize cluster first: service postgresql-9.0 initdb
 * 1) to avoid:
 * 2) /var/lib/pgsql/9.0/data is missing. Use "service postgresql initdb" to initialize the cluster first.

Data path: /var/lib/pgsql/9.0/data

Enable service: chkconfig postgresql-9.0 on service postgresql-9.0 start

Manually start: /usr/pgsql-9.0/bin/postgres -D /var/lib/pgsql/9.0/data # or /usr/pgsql-9.0/bin/pg_ctl -D /var/lib/pgsql/9.0/data -l logfile start

/usr/pgsql-9.0/bin/postgres -D /var/lib/pgsql/9.0/data -d 5
 * 1) with debugging:

/usr/pgsql-9.0/bin/postgres -D /var/lib/pgsql/9.0/data >logfile 2>&1 &
 * 1) in the background:

su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
 * 1) how to start with root:

Build Install
PostgreSQL Installation - http://www-css.fnal.gov/dsg/external/freeware/pgsql_Install.html

gunzip postgresql-version.tar.gz tar xvf postgresql-version.tar ./configure --prefix =      // default path is /usr/local/pgsql gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test

Authentication Methods
Authentication Methods - http://www.postgresql.org/docs/9.1/static/auth-methods.html

Trust Authentication
 * When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). This method should only be used when there is adequate operating-system-level protection on connections to the server.  It is usually not appropriate by itself on a multiuser machine.

Password Authentication
 * The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

Ident Authentication
 * The ident authentication method works by obtaining the client's operating system user name from an ident server and using it as the allowed database user name (with an optional user name mapping). This is only supported on TCP/IP connections.

LDAP Authentication
 * This authentication method operates similarly to password except that it uses LDAP as the password verification method. LDAP is used only to validate the user name/password pairs. Therefore the user must already exist in the database before LDAP can be used for authentication.

Remote User
Connect as another user: psql -U [user] psql -U [user] -W [database] # -W is ask for password

Create database: createdb -U [user] [database]

Create database that doesn't exist (connect to 'postgres' maintenance table) psql -U [user] -c "create database [database];" postgres

Sequences
List sequences SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

To get last value of a sequence use the following query: SELECT last_value FROM test_id_seq;

PostgreSQL: Documentation: Manuals: CREATE SEQUENCE - http://www.postgresql.org/docs/8.1/static/sql-createsequence.html

Create an ascending sequence called serial, starting at 101: CREATE SEQUENCE serial START 101;

Select next number from this sequence: SELECT nextval('serial');

Use this sequence in an INSERT command: INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

Enterprise PostgreSQL
EnterpriseDB | The Enterprise PostgreSQL Company - http://www.enterprisedb.com/

Postgres Plus Downloads - http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

UTF8 Encoding
When initializing database: su - postgres /opt/postgresql/bin/initdb -E UTF8 -D /opt/postgresql/data

To see current database encodings: psql -l

Password File
PostgreSQL: Documentation: Manuals: The Password File - http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

This file should contain lines of the following format: hostname:port:database:username:password

Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \, escape this character with \. A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine.

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.

References:
 * bash - How to pass in password to pg_dump? - Stack Overflow - http://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump

ALTERNATIVE:
 * Use the 'PGPASSWORD' environment variable

PostgreSQL vs MySQL
Read:
 * PostgreSQL's Transaction Model | Packt Publishing Technical & IT Book and eBook Store - http://www.packtpub.com/article/transaction-model-of-postgresql
 * MySQL vs PostgreSQL - WikiVS - http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#ACID_Compliance

Could not connect
could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

-bash-4.1$ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This means the database server was not started.

role does not exist
createdb: could not connect to database postgres: FATAL: role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you.

cannot drop the currently open database
Error: ERROR: cannot drop the currently open database

To fix the problem, try to issue the following command and drop the database again \connect postgres

This will connect you to the postgres database in PostgreSQL, sometimes referred to as the maintenance database.

Reference: » PostgreSQL – ERROR: cannot drop the currently open database - http://www.meeho.net/blog/2010/03/postgresql-error-cannot-drop-the-currently-open-database/

Ident authentication failed for user
Error: psql: FATAL: Ident authentication failed for user "joe"
 * 1) psql -U joe

Solution: vi /var/lib/pgsql/data/pg_hba.conf
 * To fix this error open PostgreSQL client authentication configuration file /var/lib/pgsql/data/pg_hba.conf :

This file controls:
 * Which hosts are allowed to connect
 * How clients are authenticated
 * Which PostgreSQL user names they can use
 * Which databases they can access

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options.

local  all         all                               ident sameuser host   all         all         127.0.0.1/32          ident sameuser

Append Replace with following to allow login via localhost only:

local	all	all			trust host	all	all	127.0.0.1/32	trust

NOTE: I fixed the host with extra "all" (although this doesn't appear to be required)

Now, you should able to login using following command: $ psql -d myDb -U username -W