PostgreSQL

From Omnia
Jump to navigation Jump to search

PostgreSQL

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

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: 

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

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 <database>

Delete Database: 

dropdb <database>

PostgreSQL interactive terminal program: 

psql <database>

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

psql Commands

connection

PostgreSQL interactive terminal program: 

psql <database>
psql -U <user> -W

# Regular user:
mydb=>

# Super user:
mydb=#

# To quit:
\q

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 <table_name> (
   <column_name> <column_format>(<column_size>),  -- <comments>
   ...
);

Drop table: 

DROP TABLE <table_name>;

List foreign keys: [1] 

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". [2] 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. [3]

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: [4] 

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: [5] 

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 <user>;
CREATE ROLE <user> 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 <user> LOGIN CREATEDB;

Alter user: 

ALTER ROLE <user> <changes>;
ALTER ROLE <user> LOGIN CREATEDB;

Rename role: 

ALTER ROLE [name] RENAME TO [newname];

Change password: 

ALTER ROLE <user> PASSWORD '<password>';

Drop user: 

# first drop any dependent databases:
DROP DATABASE <user>;
DROP ROLE <user>;

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: 

# psql -U user1 user2
  user2=> select * from u2;
  ERROR:  permission denied for relation u2

---

Grant role permissions on a table: [6] 

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: [7] 

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: [8] 

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: [9] 

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: [10] 

SELECT current_database();
SELECT current_user;
SELECT user;
SELECT version();

Tutorials

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): [11] 

# login as postgresql user:
# possibly: su - postgresql
su - postgres
psql
  CREATE ROLE root LOGIN SUPERUSER;
  \q

---

If you get this error, either create a 'root' database, or specify correct database 'psql mydb' 

# psql
psql: FATAL:  database "root" does not exist

If you get this error, create the root db user. 

# psql
psql: FATAL:  role "root" does not exist

---

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:

  • 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.
exclude=postgresql*

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
# to avoid:
# /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

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

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

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

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

Security

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 [12] 

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:

ALTERNATIVE:

  • Use the 'PGPASSWORD' environment variable

PostgreSQL vs MySQL

Read:

Issues

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 -U joe
psql: FATAL:  Ident authentication failed for user "joe"

Solution: [13]

  • To fix this error open PostgreSQL client authentication configuration file /var/lib/pgsql/data/pg_hba.conf :
vi /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

keywords

Retrieved from "https://aznot.com/index.php?title=PostgreSQL&oldid=796"