Postgresql on Ubuntu 10.10 cheatsheet

Here is a little cheat sheet for getting Postgress setup on Ubuntu and creating an initial database and dtabase user.


Install postgres and the python libraries:

sudo apt-get install postgresql-8.4 postgresql-client-8.4 python-psycopg2

Modify the config file to allow local connections:

sudo nano /etc/postgresql/8.4/main/pg_hba.conf

Add the line:

local     all         all     md5

Save the changes to the file and restart the server.

sudo /etc/init.d/postgresql restart

Set the password for the postgres user:

sudo passwd postgres

Change to the postgres user:

su - postgres

Create a new Database:

createdb mydb

Login to the postgres shell and point to our new database:

psql mydb

Now from the postgress shell create a user and give him access to the database:

mydb=> CREATE USER myuser WITH PASSWORD 'myPassword';
mydb=> GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
mydb=\q

Done!

To dump a database:

su - postgres

pg_dumpall dbname > outfile

Note: this puts outfile in  /var/lib/postgresql

 source