DatabaseSnips

From Custard
Jump to: navigation, search

Contents

psql crib sheet

  • psql -d <database>
  • psql -h hostname -U username -d database

psql commands

  • \q - quit
  • \dt - list tables
  • \d - describe table
  • \dx - list extensions
  • \dn - list namespaces
  • \list - list databases
  • \c <database> - connect to database


Postgres: OSX Install

  • sudo port install postgresql91
  • sudo port install postgres91-server
To create a database instance, after install do
 sudo mkdir -p /opt/local/var/db/postgresql91/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql91/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb -D /opt/local/var/db/postgresql91/defaultdb'

To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf
octopus:local jamesb$ cat ~/.bash_profile 
export PATH=$PATH:/opt/local/lib/postgresql91/bin

Postgres: Debian Install

  • sudo apt-get install postgresql-server-dev-8.4 postgresql
  • sudo psql -U pgsql
    • ALTER USER postgres with encrypted password 'xxxxxxx';
  • /etc/init.d/postgresql restart
  • sudo vi /etc/postgresql/8.4/main/pg_hba.conf
    • Change ident to md5
  • /etc/init.d/postgresql restart
  • createuser -U postgres -d -e -E -l -P -r -s jamesb
  • psql template1 # Test

Replicate Postgres Write Ahead Log to cloud

Oracle: Extract date & time from DATE field

select to_char(create_date, 'DD-Mon-YYYY HH24:Mi') as create_date from my_table

MySQL: Error 111 connecting.

This is most likely because the MySQL on the remote machine is defaulting to accepting connections from localhost only.

In /etc/mysql/my.cnf comment out the line:

bind-address = 127.0.0.1

MySQL: Log SQL statements for debugging purposes.

Run Mysqld with logging enabled.

mysqld --log=sql.log

Remember to turn off, or the log file could get enormous.

Personal tools