PostgreSQL 1A

PostgreSQL 1A

I spent the last 8 years on a lot of MySQL and little MSSQL. Now, I'm on a PostgreSQL project. Here's my cheat sheet of DBMS specific commands I needed to figure out right away.

Install on Ubuntu 8.10

$ sudo apt-get install postgresql-8.3 postgresql-server-dev-8.3
$ sudo vi /etc/postgresql/8.3/main/postgresql.conf
# uncomment the listen_addresses = 'localhost' line
# optional: change localhost to machine hostaddr if you want outside access
$ sudo /etc/init.d/postgresql-8.3 restart

Give the "root" db user a password, create a new dbuser and a database with access privs for that new dbuser. Also, give the new dbuser perms to create more databases.

$ sudo -u postgres psql template1
template1=# ALTER USER postgres WITH UNENCRYPTED PASSWORD 'P8ss';
ALTER ROLE
template1=# CREATE USER user1 CREATEDB;
CREATE ROLE
template1=# ALTER USER user1 WITH UNENCRYPTED PASSWORD 'P9ss';
ALTER ROLE
template1=# CREATE DATABASE db1 WITH OWNER user1;
CREATE DATABASE
template1=# q

To suck a sqldump into that database

$ zcat /PathToDbDump/DbDump.sql.gz | psql -h localhost -U postgres -d user1

Logging in as non-postgres user (this bit baffled me originally, until I decoded the postgres init settings): /etc/postgresql/8.3/main/pg_hba.conf has security settings that say, "If connected via unix socket then require the db username match the process owner name." By default psql uses the unix socket. That's fine for the "root" db user (postgres), but not how you want access other dbs. However, for network socket connections pg_hba.conf is set to use only password check against it's user table. Yay. So access through the socket, which is the general case in production systems anyway, as the db is on a dedicated machine.

psql --username user1 --password -h localhost template1
CREATE DATABASE db2 WITH OWNER user1;

if you don't put a db name on the psql command line, it will assume the db name is the same as the user

postgres provides an easily scripted command, createdb, that does most of what the CREATE DATABASE command does.

read the postgres docs for encrypted passwords, recommended for production

Related links