Thursday, 15 December 2011

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

No comments:

Post a Comment