My PostgreSQL Cheatsheet

As I am just starting out with Postgres, please leave me a comment if you see something that is incorrect or inaccurate!

I got these instructions on how to install Postgres on linux from Chris Oliver’s blog:

  sudo apt-get -y install postgresql libpq-dev

Postgres will create a user account on your computer called ‘postgres’. To log in for the first time to do the initial setup:

  sudo su postgres   # switch to the new user on your computer called 'postgres'
  psql               # connect to the postgres server

Once logged in as the postgres superuser, create a new user who is NOT a super user, but still has power to create databases and create other users. As far as I can tell, “user” and “role” mean exactly the same thing in late versions of Postgres:

  create role [rolename] CREATEDB CREATEROLE with PASSWORD 'myPassword';

Things are easier if you name your:

  • Linux computer username
  • Postgres username, and
  • Postgres database name

are all exactly the same

For instance, if they are all named “teddy”, then from your computer’s “teddy” user, if you type

  psql

in the command line without specifying a username, password, or database, Postgres will automatically connect you as psql user “teddy” to the database named “teddy”, and won’t prompt you for a password.

That’s why when you did sudo su postgres && psql earlier, you were connected as the psql user postgres without being prompted for a password. Of course, you can still specify a custom username, password, and database to connect to. But it is just much easier if you make everything match up from the start.

To configure Postgres so you can connect remotely to it, see this Stack Overflow entry.

To start, stop, or restart Postgres

    sudo /etc/init.d/postgresql start
    sudo /etc/init.d/postgresql stop
    sudo /etc/init.d/postgresql restart
  

Useful commands once you are in the psql program:

  \q           # quit
  \l           # list all the databases
  \d db        # list the tables, etc., in the database named 'db'
  \du          # list all the users/roles
  \connect db  # connect to the database named 'db'
  \?           # the help menu!

As far as I can tell, unless you are a superuser (and it is NOT recommended to regularly use Postgres as a superuser), you can only connect to one specific database at a time. You can’t just connect to “the whole server”. So you have to create at least one database BEFORE you can connect to psql. You can use this utility to create a db from outside the psql program:

 # create db called mydb, username is myuser, the host IP is 111.111.11.111, -e echos the drop db command sent to server so you can see what is going on
 createdb -U myuser -h 111.111.11.111 -e mydb

It is best that the postgres user who will be using the database be the one to create it, instead of the postgres super user being the one to create it. That way, the user has all the privileges they need on that database. But if a different user created the database than will be using it, then you can still grant privileges on the database to whomever needs them:

 sudo su postgres
 psql
 GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Since you cannot drop a database when you are connected to it, you can drop it from outside the psql program using this utility:

  # drop database mydb, user is myuser, host is 111.111.11.111, -i for double-confirm, -e is an echo so you can see what command was sent to the server.
   dropdb -U myuser -h 111.111.11.111 -i -e mydb

Importing and exporting CSV:

  copy TableName from '/data/my_csv_file.csv' using delimiters ';'
  \copy TableName to '~/data/my_csv_file.csv' delimiter ';' csv header;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s