PostgreSQL Quick Guide

\l (lowercase L)	list all databases
\c	use database
\dt	show tables
\dT	list datatypes
\df	list functions
\di	list indexes
\dv	list views
\du	list users
\d+	show all tables with descriptions
\d+ table	show table definition or view definition with column descriptions

Database functions

Create database
# create database mydb;
Rename database
# alter database database_name rename to new_database_name
Delete Database
# drop database mydb;

Altering a table

Renaming a table
# alter table coward rename to gallant;
Add a column to a table
# alter table gallant add column victories varchar(50);
Rename an existing column
# alter table gallant rename column victories to successes;

Backup and Restore

To backup a specific database
# pg_dump -U username -W database_name > db.sql
To backup all databases
# pg_dumpall > all.sql
To backup a specific table
# pg_dump --table table_name -U username database -f table.sql

To restore
# psql -U username -W database < db.sql
To restore a specific table
# psql -f table.sql table_name

List column names in postgreSQL

To list column names of a table, simply type

select column_name
from information_schema.columns
where table_name = 'YOUR TABLE NAME'


Installing PostgreSQL on Ubuntu

This article is intended for PostgreSQL on Ubuntu 10.x but it should be applicable on older version, perhaps with minor modifications. We would begin by installing PostgreSQL server, client, and contributed utilities.

$ sudo apt-get install postgresql postgresql-client postgresql-contrib

Next install phppgadmin, which is a GUI application for managing the database system.

$ sudo apt-get install phppgadmin

Configuring PostgreSQL

First step is to reset the admin password:

$ sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q

The # symbol indicates that you are using the superuser account. Next we change the password for the database and Linux user called postgres. Use the same password as above to avoid confusion.

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

To enable certain function of phppgadmin utility:

$ sudo su postgres -c psql < /usr/share/postgresql/8.4/contrib/adminpack.sql

Remote Access

If you wish to allow remote access, edit the following file:

$ sudo vi /etc/postgresql/8.4/main/postgresql.conf

Change the line:

#listen_addresses = 'localhost'

to

listen_addresses = '*'

and change the line:

#password_encryption = on

to

password_encryption = on

The pg_hba.conf file defines who can access the server. If you are using the server locally, set md5 and ident in the the file to trust. This would eliminate the following error:

psql: FATAL: Ident authentication failed for user "username"

without OIDS

All you need to know is that you should choose the option "without OIDS". If you wish to understand why, continue reading. OIDS are used by PostgreSQL's system tables. They refer to tables, types of data, etc. OIDS were created for system tables only, and should not be used for user tables. Unfortunately, some coders started using OIDs as default primary keys. If PostgreSQL removes OIDs, the applications coded by these guys would crash. So for the sake of backward compatibility, there is now an option "without OID".

Logging into PostgreSQL

To start using PostgreSQL, type the following command:

$ psql -d postgres -U postgres -W

-d is for the database name
-U is for the username
-W prompts the password

If you the following error message

psql: FATAL: Ident authentication failed for user "username"

edit the pg_hba.conf file and set the following to

local	all	all	ident
host	all	127.0.0.1/32	ident

to

local	all	all	trust
host	all	127.0.0.1/32	trust

Save and close pg_hba.conf and restart PostgreSQL:

$ sudo service postgresql restart

Managing users and privileges

In PostgreSQL, users own database objects such as tables and can assign privileges on those objects to other users. Database users are conceptually separate from operating system users but they can be linked. Database users are specific to the installation, not an individual database.

Creating Users

To create a user, you can use any of the following methods:

$ psql -U postgres -W
postgres=# CREATE USER name;

or

$ createuser name

or

$ sudo su postgres -c createuser name

Your options may be limited due to the system you are using, operating system account, and your configurations.

Specifying Passwords for Users

To specify a password upon creation of the user, do the following:

$ psql -U postgres -W
postgres=# CREATE USER name PASSWORD 'secret';

To specify password after creating the user, do the following:

$ psql -U postgres -W
postgres=# CREATE USER name;
postgres=# ALTER USER name WITH PASSWORD 'secret';

Dropping Users

To drop (remove) a user, you have the following options:

$ psql -U postgres -W
postgres=# DROP USER name;

or

$ dropuser name

or

$ sudo su postgres -c dropuser name

Listing Users

To see a list of existing users, type the following

$ psql -U postgres -W
SELECT * FROM pg_user;

Installing phpPgAdmin on Ubuntu

phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP. It is a very nifty tool with a very convenient GUI interface. If you have used phpMyAdmin for MySQL, this phpMyAdmin for PostgreSQL.

To install phpPgAdmin on Ubuntu 10.x, to the following:
1. sudo apt-get install phppgadmin
2. open your browser and type http://localhost/phppgadmin
3. Login and start using phpPgAdmin

If you see the error: Login disallowed for security reasons, do the following:
1. Configure your pg_hba.conf to configure client authentication. See postgre wiki
2. If this doesn't work, set extra_login_security in /etc/phppgadmin/config.inc.php file to false.

If you have other installation problems, refer to Installation FAQ

Note: be sure to backup the config files before you edit them

Visualizing PostgreSQL database

DbSchema is a really cool tool which provides powerful functionalities for visualizing, browsing and running queries. It is free and can be downloaded from http://www.dbschema.com/download.html.

Installing DbSchema on Linux

$ wget http://www.dbschema.com/dbschema.zip
$ unzip dbschema.zip
$ cd DbSchema
$ chmod +x dbschema.sh

To run DbSchema

$ ./dbschema.sh

List all tables names

The postgres SQL code for listing table names is:

select relname from pg_stat_user_tables order by relname


The PHP code to for listing table names is:

$sHost = 'localhost';
$sDatabase = 'aaaa';
$sUser = 'bbbb';
$sPassword = 'cccc';

$conn = pg_connect("host=$sHost dbname=$sDatabase user=$sUser password=$sPassword") 
          or die('unable to connect to database');
$q = 'select relname from pg_stat_user_tables order by relname';
$result = pg_query($conn, $q) or die ("Error in query: $q - " . pg_last_error($q));        

while ($rw = pg_fetch_array($result)) {
    print $rw[0] . '<br>';
}