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;

---

Data Types#

smallint	2
integer	4
bigint	8
real	4
double precision	8
smallserial	2
serial	4
bigserial	8
money	8
char(n)
varchar(n)
text	variable length, unlimited
bytea	binary, variable length
timestamp	date and time	not null default current_timestamp
date

---

Passing a password into psql#

export PGPASSWORD=ThePassword

---

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

psql mydb postgres -f file-name >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.