So, you just installed your PostgreSQL, and you have no idea how to use it – there is no icon in the menu of your OS, so how can you use it? Well, with the dreadful command line.

Of course – some people will never get used to textual programs. They need a GUI. That's fine. Not understandable for me, but who am I to judge. But knowing at least a basic things about standard command line tools for PostgreSQL can save you a lot of headache in some cases. Plus – you always have them so these are treated as default programs to use.

There are many tools, but I think that you should know three of them. At least to the point where you're not afraid of them, and feel reasonably comfortable using them. This holy trinity is:

1. psql
2. pg_dump
3. pg_restore

First, let's start with psql. When you'll first run it, you will see something more or less like this:

=$ psql
psql (9.3devel)
TYPE "help" FOR help.
 
postgres=#

Immediately we see some interesting information. For starters – it tells us what is the version of psql (and PostgreSQL server) I'm using.

If, for whatever reason, I would use psql in version that is not the same as version of PostgreSQL server, I would see:

psql (9.1.3, server 9.3devel)
WARNING: psql version 9.1, server version 9.3.
         SOME psql features might NOT WORK.
TYPE "help" FOR help.

So, we know that I'm using 9.3devel version. Next, I see some welcoming “help" message, and a prompt. The “postgres" in prompt does not relate to name of the product – i.e. PostgreSQL database server, but is a name of database that I am connected to.

This is important. MySQL allows you to connect to database server, but not being connected to any specific database. Then you issue “USE …" statement, or something similar to connect to whatever database you're interested in. In PostgreSQL – when you have connection with server, the connection is always to one particular database.

Now – the help message. What does it show?

postgres=# help
You are USING psql, the command-line interface TO PostgreSQL.
TYPE:  \copyright FOR distribution terms
       \h FOR help WITH SQL commands
       \? FOR help WITH psql commands
       \g OR terminate WITH semicolon TO EXECUTE query
       \q TO quit
postgres=#

Immediately you can notice something – all the things it tells you to type start with backslash.

This is a convention – all psql commands (not PostgreSQL! psql – as in: command line client) start with backslash. With the only (known to me) exception: help.

You can try, of course all the various commands it told you, but, if possible, try to wait until you'll be done with this blogpost.

The first, most basic, thing you have to know, is of course: how to exit the damn thing. Help text stated that it's \q, and indeed it works. But I, personally, prefer to use a keyboard shortcut. If you're not familiar with command line tools in general (and I assume you're not, since otherwise you wouldn't need to read this blogpost) you might not know but there is quite commonly used key combination, which is ctrl-d (that is: press control/ctrl key, and while pressing it press letter d).

OK. If you know how to exit psql, now for – what you can do with it.

Of course – you can use it to run SQL queries. As help page said – use \g or ; to run it:

postgres=# SELECT 1;
 ?COLUMN?
----------
        1
(1 ROW)
 
postgres=# SELECT 2\g
 ?COLUMN?
----------
        2
(1 ROW)

Some people forget that commands have to be ended, and press enter, at which point they are at loss, since “nothing happens":

postgres=# SELECT 3
postgres-#

Important thing to notice is that prompt has subtly changed. Instead of “=" it now contains “-“. This means that psql is waiting for continuation of the SQL command. Because you might want to have multiline-commands:

postgres=# SELECT
postgres-# 1,
postgres-# 2
postgres-# ;
 ?COLUMN? | ?COLUMN?
----------+----------
        1 |        2
(1 ROW)

There are also other possibilities for this character:

postgres=# SELECT (
postgres(# '
postgres'# ab
postgres'# '
postgres(# )
postgres-# ;
 ?COLUMN?
----------
         +
 ab      +
 
(1 ROW)

Note the ( and ‘ characters in prompt – they show what has been opened, but not ended yet.

One great thing about psql, and something I miss a lot when I have to use mysql (program – command line interface to MySQL database), is another great keyboard shortcut – ctrl-c. In MySQL, it terminates the client, but in psql – like in bash, for example – it just cancels currently entered command, and lets you start writing new one:

postgres=# SELECT 1 + 2^C
postgres=#

Now that you know how to run some basic queries, let's see what other things you can get psql to do.

Of course – we can ask it to show list of databases, or tables.

To do it, you use “\l" and “\d" psql commands:

postgres=# \l
                                     List OF DATABASES
      Name      |     Owner      | Encoding |   COLLATE   |    Ctype    |    Access privileges
----------------+----------------+----------+-------------+-------------+-------------------------
 postgres       | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres            +
                |                |          |             |             | postgres=CTc/postgres
 template1      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres  +
                |                |          |             |             | =c/postgres
(3 ROWS)
 
postgres=# \d
No relations found.

Technically \d does more, and it's not always good idea to use it, but I'll get to it later.

For reasons that are not really important, \d used word “relations". In case you're not sure what this means you can (for now) assume it's basically the same as table.

When I'll create some tables, I will see them, with some more or less interesting information:

postgres=# CREATE TABLE t1 (i int4);
CREATE TABLE
postgres=# CREATE TABLE t2 (i int4);
CREATE TABLE
postgres=# \d
       List OF relations
 Schema | Name | TYPE  | Owner
--------+------+-------+--------
 public | t1   | TABLE | depesz
 public | t2   | TABLE | depesz
(2 ROWS)

Now, using the same \d, I can get information about how the table “looks like":

postgres=# \d t1
      TABLE "public.t1"
 COLUMN |  TYPE   | Modifiers
--------+---------+-----------
 i      | INTEGER |

This is the thing you need to do, when someone asks you on IRC for schema of your table, or sometimes even “\d of your_table". Output of \d will contain all indexes, primary key, unique constraints, foreign keys, checks, constraints, and virtually anything you might need to know about a table.

In some databases there exists a concept of SCHEMA. It is a kind of namespace. In the examples above, the schema is public, and it's the default schema. But there are others. To list them you use \dn command (n for namespace):

postgres=# \dn
List OF schemas
  Name  | Owner
--------+-------
 public | pgdba
(1 ROW)

Here is important bit of information: psql, but default, doesn't show you system schema/tables. To see them all, you just add uppercase letter S:

postgres=# \dnS
      List OF schemas
        Name        | Owner
--------------------+-------
 information_schema | pgdba
 pg_catalog         | pgdba
 pg_temp_1          | pgdba
 pg_temp_2          | pgdba
 pg_temp_3          | pgdba
 pg_toast           | pgdba
 pg_toast_temp_1    | pgdba
 pg_toast_temp_2    | pgdba
 pg_toast_temp_3    | pgdba
 public             | pgdba
(10 ROWS)

(you can also do it with \d – i.e. use \dS, but I will not paste the output as it's too long).

So, how does psql show you results. Assuming your terminal has 132 characters, if you'll select from table that has many columns, you'll see something like this:

postgres=# SELECT * FROM pg_class LIMIT 1;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relall
visible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids
| relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid |        relacl         | reloptions
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+-------
--------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------
+------------+-------------+----------------+----------------+--------------+-----------------------+------------
 pg_statistic |           11 |   10817 |         0 |       10 |     0 |       11795 |             0 |       21 |       388 |
     12 |          2840 |             0 | t           | f           | p              | r       |       26 |         0 | f
| f          | f           | f              | f              |          712 | {pgdba=arwdDxt/pgdba} |
(1 ROW)

This doesn't really look good. But there are couple of ways to solve the problem.

First, is usage of extended output. To turn it on, or back off, you issue “\x" command:

postgres=# \x
Expanded display IS ON.

And then, when you'll again run this query, you'll get:

postgres=# SELECT * FROM pg_class LIMIT 1;
-[ RECORD 1 ]--+----------------------
relname        | pg_statistic
relnamespace   | 11
reltype        | 10817
reloftype      | 0
relowner       | 10
relam          | 0
relfilenode    | 11795
reltablespace  | 0
relpages       | 21
reltuples      | 388
relallvisible  | 12
reltoastrelid  | 2840
reltoastidxid  | 0
relhasindex    | t
relisshared    | f
relpersistence | p
relkind        | r
relnatts       | 26
relchecks      | 0
relhasoids     | f
relhaspkey     | f
relhasrules    | f
relhastriggers | f
relhassubclass | f
relfrozenxid   | 712
relacl         | {pgdba=arwdDxt/pgdba}
reloptions     |

Much better, isn't it?

There is also another way.

In your shell, set PAGER environment to value “less":

export PAGER=less

This possibly should be added to your ~/.bash_profile, or something like this.

Also, configure “less" properly. I use this:

export LESS='-iMFXSx4R'

with these setting, I get both vertically, and horizontally, scrollable screen.

Yet another option, is to redirect output to file.

This can be done from within psql using \o command, like this:

postgres=# \o /tmp/test.output
postgres=# SELECT * FROM pg_class;
postgres=# SELECT 123;
postgres=# \o
postgres=# SELECT 256;
 ?COLUMN?
----------
      256
(1 ROW)

Outputs from select * from pg_class and select 123; both were redirected to /tmp/test.ouput file, and now you can view it using any tool you want.

Of course, just like \o outputs text, \i can be used to read commands from file, and run them.

For example, let's create /tmp/test.sql file, with following content:

SELECT COUNT(*) FROM pg_class;
\x
SELECT oid, relname FROM pg_class LIMIT 2;
\x
SELECT 123;

As you can see it contains both SQL queries and psql \* commands (in this case \x only), and when I'll run it:

postgres=# \i /tmp/test.sql
 COUNT
-------
   293
(1 ROW)
 
Expanded display IS ON.
-[ RECORD 1 ]---------
oid     | 2619
relname | pg_statistic
-[ RECORD 2 ]---------
oid     | 1247
relname | pg_type
 
Expanded display IS off.
 ?COLUMN?
----------
      123
(1 ROW)

Please note that both \o and \i can be “simulated" from shell, using simply redirects:

psql < /tmp/test.sql
<psql < /tmp/test.sql > /tmp/test.ouput

Another command that I think you should know by heart, is: \e. When you'll enter it your editor of choice will be ran, so you will be able to edit the query in possibly more convenient way. But what exactly is editor of choice? Well, it depends on environment variables. In this particular case, psql runs command that is in one of these environment variables:

1. PSQL_EDITOR
2. EDITOR
3. VISUAL

So, if your editor of choice is, for example, kate, you can:

=$ export PSQL_EDITOR=kate
=$ psql

I, personally, prefer to have it set to “/usr/bin/vim -c ‘:set ft=sql'".

There are, of course, many more commands you can use – but I wanted to keep this as basic as possible. The last two commands I will mention in here, are your gateways to more information. First is “\?" – this shows list of all \* commands, and it's quite long (98 lines in my psql).

The other one, is one of the best things that psql can do to help you. It's the \h command.

When you'll type: \h, it will show you list of all possible SQL query “types", it's pretty long, so I will not copy paste it in here. And when you'll type: \h some_command – you will get syntax information for this command. For example:

postgres=# \h CREATE INDEX
Command:     CREATE INDEX
Description: define a NEW INDEX
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON TABLE_NAME [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = VALUE [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

And you can get this help for every query that you want. This helps me a lot, whenever I forget order of operations, or what exactly is possible to do, and so on.

In here, I'd like to conclude my review of psql. That doesn't mean I described what it can do. But – if you can't stand console-mode, you will probably not use psql anyway, and you just need the basics, so you will be able to provide requested information, when looking for help on irc.

And if you'll like psql, you will read \?, \help, and of course the fine manual.

On to pg_dump.

This is the way to make dumps/backups of the database. Of course there are other ways, but this is the most basic thing.

When run with –help, it shows quite a lot of information, I will just show some examples.

First, to get full dump of a database, you just do:

pg_dump database_name

That's all. Of course – you might need to provide –username/–host/–port, but no other options are needed.

Dump, in such case is printed to screen, and looks more or less like this:

=$ pg_dump postgres
--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = ON;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
 
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: pgdba
--
 
COMMENT ON DATABASE postgres IS 'default administrative connection database';
 
...

It will contain series of SQL commands, each prepended with meta-data comment, which create all tables, views, indexes, and so on, and of course load the data.

In some cases, you might want the dump, to contain also “DROP" commands, for example to be able to load the dump to database that contained previous version of the database (in which case normal dump, on loading, would cause errors due to creation of already-existing tables). This is done using:

=$ pg_dump --clean postgres
--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = ON;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
 
SET search_path = public, pg_catalog;
 
DROP TABLE public.t2;
DROP TABLE public.t1;
DROP EXTENSION plpgsql;
DROP SCHEMA public;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: pgdba
--
 
COMMENT ON DATABASE postgres IS 'default administrative connection database';
...

Alternatively you can make the dump that contains “CREATE DATABASE" command, but I personally don't recommend it. It's more flexible if name of database that data is loaded to is not hardcoded in dump.

One, very, very helpful option is: –schema-only option, which together with –table, gives you ability to do:

=$ pg_dump --schema-only --table t1 postgres
--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = ON;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
 
SET search_path = public, pg_catalog;
 
SET default_tablespace = '';
 
SET default_with_oids = FALSE;
 
--
-- Name: t1; Type: TABLE; Schema: public; Owner: depesz; Tablespace:
--
 
CREATE TABLE t1 (
    i INTEGER
);
 
 
ALTER TABLE public.t1 OWNER TO depesz;
 
--
-- PostgreSQL database dump complete
--

That is – get SQL command to create given table, and just this table.

Of course, output from pg_dump should be sent to a file, not to screen, and this can be done using shell redirect:

pg_dump database_name > some_file

or using –file switch:

pg_dump --file=some_file database_name

All the dumps shown above were done using normal, human-readable format. Which has certain benefits (mostly being human readable), but it makes certain things harder.

Luckily, there is, much better, custom format. To use it, you just do:

pg_dump --format=custom --file=some_file database_name

The most immediate difference is how you restore from dump. When you use normal, plain text format – to load the dump you would use psql. Using “psql -f dump.file", or psql's “\i" command, or perhaps using redirection, like “psql < dump.file".

But when you use custom format, to restore you have to use pg_restore tool, which has great capabilities, including, but not limiting to, loading data, from single dump, in multiple parallel threads, and loading just parts of dump file.

But before I will go to pg_restore, let me just finish quickly information about pg_dump.

Basically the only option that I want to cover is –data-only option. Just like “–schema-only", it modifies what will be dumped – this time, by removing all information about tables/ views, and alike, and dumping just the data from all tables (or single table if –table would be used).

The problem with data-only dumps is, that while they look like a sensible solution – they quite often cause problems. This is because Pg, when loading data, will load it to pre-existing tables. With their possibly existing foreign keys.

In many cases pg_dump is smart enough to order data so that loading will work, but in some cases, you will see:

=$ pg_dump --data-only postgres
pg_dump: NOTICE: there are circular foreign-KEY constraints among these TABLE(s):
pg_dump:   t2
pg_dump: You might NOT be able TO restore the dump WITHOUT USING --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider USING a FULL dump instead OF a --data-only dump to avoid this problem.
...

This means that there will be some issues when loading the dump. Options are listed, and of course running full dump is preferable, but the –disable-triggers option is interesting. This makes dump look like this:

--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = ON;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
 
SET search_path = public, pg_catalog;
 
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: depesz
--
 
SET SESSION AUTHORIZATION DEFAULT;
 
ALTER TABLE t1 DISABLE TRIGGER ALL;
 
COPY t1 (i) FROM stdin;
\.
 
 
ALTER TABLE t1 ENABLE TRIGGER ALL;
 
--
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: depesz
--
 
ALTER TABLE t2 DISABLE TRIGGER ALL;
 
COPY t2 (i) FROM stdin;
\.
 
 
ALTER TABLE t2 ENABLE TRIGGER ALL;
 
--
-- PostgreSQL database dump complete
--

That is, before loading data to any table, triggers are disabled, and then re-enabled afterwards.

One issue with this is: consider when you want to use data-only dumps. To be able to sensibly use them you need to remove all rows from tables before you will load such dump. And this means that (in non-trivial databases) it will be problematic: find all tables, remove all rows using DELETE or TRUNCATE, and then load dump.

Really, really – I have yet to see a case when using –data-only makes sense. Especially since you can use custom format, and just “extract" data from it.

As always, if you want to know more the fine manual is the best source of information.

Now, for the final piece: pg_restore.

As I wrote earlier, to use pg_restore on dump, it has to be made using “custom" format.

This can be done using:

=$ pg_dump --format=custom --file=test.dump postgres

To load such dump to existing database, you do:

pg_restore --database=testdb test.dump

When ran without –database option, it will “decode" test.dump to SQL format, and print it to terminal – basically making the same output as pg_dump in plain format.

If you'll ever want to load large dump, with lots of data, indexes and so on, you will praise its (pg_restores) –jobs option, which lets you trivially speed up restoration by parallelization of restoration tasks. Usage is trivial:

pg_restore --database=testdb --jobs=16 test.dump

But, the single best thing about pg_restore is ability to use custom restoration lists.

First you need to generate content list for dump. This is done using –list option:

=$ pg_restore --list test.dump
;
; Archive created at Mon DEC 31 17:21:52 2012
;     dbname: postgres
;     TOC Entries: 15
;     Compression: 9
;     Dump Version: 1.12-0
;     Format: CUSTOM
;     INTEGER: 4 bytes
;     Offset: 8 bytes
;     Dumped FROM DATABASE version: 9.3devel
;     Dumped BY pg_dump version: 9.3devel
;
;
; Selected TOC Entries:
;
1972; 1262 30048 DATABASE - postgres depesz
6; 2615 2200 SCHEMA - public pgdba
1973; 0 0 COMMENT - SCHEMA public pgdba
1974; 0 0 ACL - public pgdba
171; 3079 11783 EXTENSION - plpgsql
1975; 0 0 COMMENT - EXTENSION plpgsql
169; 1259 30049 TABLE public t1 depesz
170; 1259 30052 TABLE public t2 depesz
1966; 0 30049 TABLE DATA public t1 depesz
1967; 0 30052 TABLE DATA public t2 depesz
1962; 2606 30056 CONSTRAINT public t1_pkey depesz
1964; 2606 30058 CONSTRAINT public t2_pkey depesz
1965; 2606 30059 FK CONSTRAINT public t2_i_fkey depesz

Lines starting with “;" are comments.

You can redirect the list to file, edit it, removing any lines you want, and then use for restoration:

=$ cat modified.list
171; 3079 11783 EXTENSION - plpgsql
1975; 0 0 COMMENT - EXTENSION plpgsql
1965; 2606 30059 FK CONSTRAINT public t2_i_fkey depesz
 
17:27:02 depesz@h3po4 ~
=$ pg_restore --use-list=modified.list test.dump
--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = ON;
SET check_function_bodies = FALSE;
SET client_min_messages = warning;
 
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
 
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
 
 
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
 
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 
 
SET search_path = public, pg_catalog;
 
--
-- Name: t2_i_fkey; Type: FK CONSTRAINT; Schema: public; Owner: depesz
--
 
ALTER TABLE ONLY t2
    ADD CONSTRAINT t2_i_fkey FOREIGN KEY (i) REFERENCES t2(i);
 
 
--
-- PostgreSQL database dump complete
--

As you can see, I left in modified.list just 3 lines – for extension, its config, and one foreign key. And that's precisely what got restored.

This feature is simply indispensable when dealing with many databases, and I use it on virtually daily basis. Potential use-cases include creation of simple backups of single objects when modifying them, extracting similar functions to be able to textually compare them, and more.

Pg_restore of course also has manual, but it's help information (pg_restore –help) should be enough usually.

This blogpost definitely did not cover everything. I didn't show how to automatically create batch updates of large tables in psql, or how to customize its prompt. Or how to use pg_dump to copy databases, but I think it covers the basics. If you are a GUI user, consider using them for a bit longer – the first notion that “it can do very little", “it doesn't show me all information that I'm used to" relatively quickly should be changed into “it's amazing how fast it is and what it actually can do".