!psql - command line database query tool

{{{psql  [-h 192.168.1.1]  db-name  user-name}}}

user-name is often postgres

to save all output:  
{{{psql db-name  user-name  2>&1 |tee  log}}}

---------------------------------------------------------------------------
!psql commands

{{{
\q		quit
\l		list databases
\c db		connect to db
\dt		list tables
\d table        display schema for table
\i file		input file
\?		list \ commands
\h		list sql commands
}}}
{{{
show all;
show server_version;
show config_file;
show data_directory;
show hba_file;
}}}
{{{
create database mydb;
drop database mydb;
}}}
---------------------------------------------------------------------------

!Running a report from the command line:

{{{
psql mydb postgres -c 'select screen_id, substring(filename from 24) as "File Name", name from screen order by filename;' >output.txt
}}}

---------------------------------------------------------------------------

!Dumping a single DB
{{{
pg_dump [-h 192.168.1.6]  [options]  db-name  -U  user-name  |gzip  > file.dump.gz

	-s			schema only
	-a --column-inserts	data only

	no option means schema and data
}}}

To pass the password in a shell script do:
{{{
export PGPASSWORD=xxxxx
}}}

---------------------------------------------------------------------------

!Restoring a single DB

Everything done through psql

Drop existing stuff first if pre-existing:
{{{
	psql mydb postgres
	drop schema public cascade;
	create schema public;
}}}
Otherwise:
{{{
	psql ...  postgres postgres
	create database mydb;
	\c  mydb
}}}
Then:
{{{
	\i  file.dump
}}}
---------------------------------------------------------------------------

!Dumping an entire (all) DB's
{{{
pg_dumpall -U postgres |gzip >file
}}}
---------------------------------------------------------------------------

!CSV file format

Exporting:
{{{
copy (select lname, fname, personal_email from person where personal_email <> '')
to '/tmp/file.csv'
with (format csv);
}}}

Importing:
{{{
copy person (user_id, user_lname, user_fname, email_address, user_password, birth_date, sex, when_added)
from '/tmp/sample-users.csv'
with (format csv);
}}}

The csv files must be in the /tmp directory or somewhere that is globally readable because this command is run by a process that is connected to the postgres user.  It, therefore, can only access files that it has permission to read.

The import command may exclude the column list if all the columns are used and all of the columns are in the order specified in the schema.


---------------------------------------------------------------------------

!Starting & stopping the server
{{{
su postgres
export PGDATA=/user/...
pg_ctl stop

pg_ctl -l ~/logfile start


pg_ctlcluster 8.3 main restart
}}}
---------------------------------------------------------------------------

!Configuration

Initially, only the postgres user can access the database.  All
configuration should be done from the postgres user as follows:
{{{
	su
	su postgres
}}}
Configuration on a running system can be discovered via psql using:
{{{
	show all;
	show hba_file;
	show config_file;
	show data_directory;
}}}
Do a "show all;" in psql and check lc_*.  It must say "C" and not
"en_US.UTF-8" or spaces and - will not be significand in sorts.  If it
is wrong you must delete and re-create the entire instance. (show below)

You must give postgres a password, do from psql:
{{{
	alter user postgres with password 'postgres';
}}}
Configuration changes can be recorded via:
{{{
	pg_ctl reload
	    or
	pg_ctlcluster 8.1 main reload
}}}

Access is controlled by file:   {{{pg_hba.conf}}}

The following line must be in there for JDBC to work:
{{{
	host	all	all	127.0.0.1/32	md5
}}}
The following line allows local users to use the DB with the password
{{{
	local	all	all	md5
}}}
The following line allows local users to use the DB without a password:
{{{
	local	all	all	trust
}}}
The following line allows remote machines on the same sub-domain to
use the DB with password:
{{{
	host	all	all	192.168.1.0/24	md5
}}}
Or, you can use the following to inhibit SSL communications:
{{{
	hostnossl	all	all	192.168.1.0/24	md5
}}}
The /24 means only the first 24 bits of the address (192.168.1) are significant

If other machines are to use the DB you also have to enable them via the
postgresql.conf file.  If this file is changed you must re-boot postgres.
Making it reload is not enough.  Make the following changes:
{{{
	listen_addresses = '*'
	port = 5432
}}}
listen_address doesn't list address of machines that can connect!  It lists
ports that postgres will listen from.  This is only useful if you have
multiple ethernet cards on the postgres box.  Use either localhost or *.
	
----------------------------------------------------------------------

!Max connections

The maximum number of connections allowed on the database is controlled by the {{{postgresql.conf}}} file via the {{{max_connections}}} setting.  In general, each web service uses one connection.  The default maximum is 100. If this setting is changed the database must be stopped and restarted. 

	
----------------------------------------------------------------------

!Creating a database

When creating the DB do:
{{{
	initdb  --locale=C
}}}
If you don't do this (--locale=C) the system will ignore leading
spaces and `-' when sorting.