Description of PostgreSQL database tables installed on a particular computer

Question

A bit over a year ago I installed PostgreSQL on six computers. On one of those computers, I imported 2 gig of census data from CSV files.

Historically I have just worked with flat files, but in this case the files are so big they choke my analysis software. I am new to both PostgreSQL and relational databases in general, and I have a very basic beginner's question: What software (e.g. pgAdmin III) and what and commands would I use to quickly answer the following questions on each machine:

1. Is PostgreSQL in still installed and running on each machine?

2. (If 1 is yes) Does the machine in question have installed any non-bundled tables or data?

3. (If 2 is yes) How can I produce a summary description of the tables that are installed?

In terms of a summary description, I am hoping for the table name, a list of column names, the data type of each, and the number of lines or records in each table, and possibly any additional database-relevant facts like whether the column is a key or indexed.

I work mainly under Windows 7 & 8, though I have a virtual Ubuntu macine installed on one computer.

Answer 1

Is postgreSQL in still installed and running on each machine?

The method you would use to find that out will depend on the operating system on each machine.

On Linux hosts you could use this:

ps -ef | grep postgres

If you see a process named postgres then postgresql is installed and running.

If not, it may be installed but not running. You could check the package management system of your distro to check if it is installed, for example on RPM based systems:

rpm -qa "*postgres*"

On a Windows machine you may be able to see if it is running using the task manager. To check if it is installed go into the Control Panel "Programs and Features" option.

Does the machine in question have installed any non-bundled tables or data?

By non-bundled I assume you mean tables or data other than the system catalogs that are created when you install the system.

My preference is to interact via the command line psql interface. Once you get the psql prompt you can use various 'backslash commands' to inspect the database.

To open the psql command - well you will need appropriate credentials. The details are going to depend on how you configured things when you installed it. If you happen to be using Linux, and you have root access, then the easiest way is to su to the postgres Linux user first, which in most cases will be able to connect directly to the database:

$ sudo su - postgres
$ psql

To see what databases exist use the \l command:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 harmic    | harmic   | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 postgres  | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 template0 | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

The databases postgres, template0 and template1 are created by the system so the only database containing user data is 'harmic' in this case.

To connect to a database use \c:

postgres=# \c harmic
You are now connected to database "harmic" as user "postgres".

To list all the tables that exist in this database, including system catalogs use \dt:

harmic=# \dt+ *.*
                                     List of relations
       Schema       |          Name           | Type  |  Owner   |    Size    | Description 
--------------------+-------------------------+-------+----------+------------+-------------
 information_schema | sql_features            | table | postgres | 96 kB      | 
 information_schema | sql_implementation_info | table | postgres | 48 kB      | 
 information_schema | sql_languages           | table | postgres | 48 kB      | 
 information_schema | sql_packages            | table | postgres | 48 kB      | 
 information_schema | sql_parts               | table | postgres | 48 kB      | 
 information_schema | sql_sizing              | table | postgres | 48 kB      | 
 information_schema | sql_sizing_profiles     | table | postgres | 8192 bytes | 
 pg_catalog         | pg_aggregate            | table | postgres | 40 kB      | 
 pg_catalog         | pg_am                   | table | postgres | 40 kB      | 
... etc
 public             | aaa                     | table | harmic   | 16 kB      | 
 public             | entry                   | table | harmic   | 8192 bytes | 
 public             | exams                   | table | harmic   | 8192 bytes | 
(60 rows)

The tables listed in the Schema's 'information_schema' and 'pg_catalog' are not user tables. pg_catalog contains the internal information used by the database to keep track of everything in the database, and information_schema contains information tables about the database which are standardized by the SQL standard. In my case there are a few tables in the 'public' schema that are actual user tables.

How can I produce a summary description of the tables that are installed?

To see a full description of one or more tables:

harmic=# \d public.aaa
      Table "public.aaa"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 

The above trivial table has two columns named a and b, of types integer and text respectively.

You can use wildcards to get this listing printed for matching tables, eg:

harmic=# \d public.*

You can get an exact number of rows that exists in each table by executing:

SELECT count(1) FROM aaa;

(where aaa would be the table name).

Doing this for each and every table could be slow and tedious. You can get an approximation of the number of rows in all tables by inspecting some tables in the pg_catalog like this:

harmic=# SELECT nspname as schemaname,
harmic-#        relname as tablename,
harmic-#        reltuples as approx_rows 
harmic-# FROM pg_class LEFT JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE nspname='public' and relkind='r';
 schemaname | tablename | approx_rows 
------------+-----------+-------------
 public     | exams     |           3
 public     | entry     |           2
 public     | aaa       |           2
(3 rows)

Note that the number of rows shown is updated whenever the database is analysed. The autovacuum daemon does this automatically from time to time, but you can also manually trigger it using command ANALYZE;

Answer 2

Question 1: What OS are you running? If its a sensible one(*nix), try running psql in the terminal of any of them. That will definitely tell you if psql is running. Whether postgres is installed is a totally different question. Determining that is very situation specific (What OS do you have?).

Question 2: Im not familiar with bundling tables. But simple inspection of the GUI provided by pgAdmin would be your best bet. What is bundling?

Question 3: Best way to generate a summary of a series of tables would be to run raw sql :) The following will answer most of your described needs. Connect to psql and run something like the following:

• connect <database_name>

• \d (will list all the tables in the database)

• \d <tablename> ('describes' the table)

• select count(*) from <tablename> (returns the total number of rows in the table.)

This is the best place for postgres stuff: http://www.postgresql.org/docs/

Bloody boring reading, but very simple and to the point.

Best of luck!