Postgres Notes

postgres-notes.mdown

Postgres Notes

Controlling Postgres

Start Postgres

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Stop Postgres

$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

Working with databases

Create a Database

$ createdb mydb

Remove a Database

$ dropdb mydb

To start working with a database and use the Postgres terminal

$ psql mydb

Common Commands

Login as "postgres" (SuperUser) to start using database: # su - postgres

Create a new database: $ createdb mydb

Drop database: $ dropdb mydb

Access database: $ psql mydb

Get help: mydb=# \h

Quit: mydb=# \q

Read command from file: mydb=# \i input.sql

To dump a database: $ pg_dump mydb > db.out

To reload the database: $ psql -d database -f db.out

Dump all database: # su - postgres # pg_dumpall > /var/lib/pgsql/backups/dumpall.sql

Restore database: # su - postgres # psql -f /var/lib/pgsql/backups/dumpall.sql mydb

Show databases: #psql -l or mydb=# \l;

Show users: mydb=# SELECT * FROM "pg_user";

Show tables: mydb=# SELECT * FROM "pg_tables";

Set password: mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';

Clean all databases (Should be done via a daily cron): $ vacuumdb --quiet --all

postgresql-covert-mysql.mdown

Converting MySQL Databases to Postgresql

Download mysql2postgres: $ cd ~/code $ git clone git://github.com/maxlapshin/mysql2postgres.git

Install the needed gem packages for mysql2postgres to work: export ARCHFLAGS="-arch i386 -arch x86_64" gem install mysql -- --with-mysql-dir=/usr/local
--with-mysql-config=/usr/local/bin/mysql_config

export ARCHFLAGS="-arch x86_64"
gem install pg -- --with-pg-dir=/usr/local \
--with-pg-config=/usr/local/bin/pg_config \
--with-pg-include=/usr/local/include \
--with-pg-lib=/usr/local/lib

Configure the mysql2postgres config file: $ cd mysql2postgres $ cp config.yml.sample config.yml $ mate/vim config.yml

Run the Ruby mysql2postgres script:

$ ./mysql2postgres