Show Tables equivalent in Postgresql

The equivalent of mysql 'show tables' command in postgresql:

\dt

To see more information about the table, do \dt with the table name:

\dt table_name

If you want to see more than just tables (eg. sequences), use:

\d

To see all databases, like 'show databases' in mysql:

select datname from pg_database;

Backing Up and Restoring Postgresql Databases

I recently have defected from Gentoo to Ubuntu, (*Gasp*, but I'm finally sick of the constant waiting from compiling and re-compiling, or suffering from dependencies breakage or conflicting masks preventing me from updating between new and old packages and various other complaints, but I'll leave that to another day), which one of the things that I had to do was to perform a backup of all my stuffs, including the data on my databases.

For Postgresql, it is really straightforward, and if anybody did a RTFM of Postgresql's man pages, you'll have probably found the answer. But if you're just lazy and wanted to consult the 'Oracle of Mountain View' and be spoonfed, here's how. To back it up:

pg_dumpall > db.out

Where db.out is your output file. Here's how you restore the database back into it's original state:

psql -f db.out postgres

Setting up Sequel for Postgresql

For those people who are using postgresql with sequel, just in case if you are having an error message like the one below:

Sequel::Error::AdapterNotFound: Could not load postgres adapter:
no such file to load -- postgres
    from /usr/local/lib/ruby/gems/1.8/gems/sequel_core-1.0.5/lib/sequel_core/database.rb:383:in `adapter_class'
    from /usr/local/lib/ruby/gems/1.8/gems/sequel_core-1.0.5/lib/sequel_core.rb:36:in `method_missing'

This probably means that you don't have the postgres adapter installed. One quick way to fix problem is to install the postgres gem via rubygems:

% rubygems install -r postgres

That should solve the dependency requirement problem.

Looking for Postgres Configuration's Path

Spent a rather long amount of time fumbling around trying to find where postgres's configuration files are resided. It didn't help at all that all postgres's configurations files are not found in the global '/etc' directory. Only after doing some source diving into postgres's SysV initialization script did I find where the locations of 'postgresql.conf' are:

Gentoo:

/var/lib/postgresql/data

Redhat:

/var/lib/pgsql/data

That's annoying because the configuration files, are stored in different locations for different distributions. Furthermore default configuration flags are different for distros as well, which I had to spent more time to look for the errors while trying to make sequel work with both distros, so it works whether if I'm using my standalone box or the server.

Also, it turns out that Gentoo has enabled localhost access by default, while RedHat didn't. So to 'fix' RedHat's 'pg_hba.conf' file, and uncomment localhost access to the database:

# IPv4-style local connections:
host    all         all         127.0.0.1         255.255.255.255   trust

Talk about 'distro fragmentation hell', which is no better than 'DLL hell' that Windows users suffer from!

Setting Up Postgresql

Was mucking around with postgresql again, given that I've decided to start everything at right footing, rather than to stick with mysql, which is what I'm more used to, for various reasons I've remembered from my database lectures in the past.

Installation's a little bit different from mysql, but with a bit of Googling, there are some pretty good instructions out there that makes things clearer. Subsequently after installation, there may be two error messages to take note of, which you may encounter after the install process:

psql: FATAL:  user "vincent" does not exist

If you're not running as user 'postgres', this error indicates that the user (in my case 'vincent') does not exist in the database. Upon installation, postgres only allow the user 'postgres' to access the database by default, so it's necessary have the right user profile to make configuration changes. This is done by invoking 'su' (or 'sudo' for some users) into 'postgres' and invoke the 'createuser' command:

% createuser
Enter name of user to add: vincent
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y

The rights allow users to create databases, and to add new users to the database, which on a standalone machine that I have, it's probably ok to have 'y' for both.

You're not done with the process yet if you haven't created your database for access, and if you executed 'psql' again, the following error props up:

psql: FATAL:  database "vincent" does not exist

This is relatively easy to fix. Since you've already have database creation access, just by issuing 'createdb' will initialize your database for use.

% createdb
CREATE DATABASE

Time to start creating and populating tables from here!