Top 5 biggest tables in currently active scheme in PostgreSQL

SELECT C.relname                                     AS table_name,
       pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
       S.n_live_tup                                  AS total_rows
FROM pg_class C
       LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
       LEFT JOIN pg_stat_user_tables S ON (C.relname = S.relname)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;

Drop all active connections to PostgreSQL server

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db' -- ! replace db with the target database name
  AND pid <> pg_backend_pid();

Sync your development environment with a remote one

While the developing process you need sometimes to sync you local development database with a remote one (i.e. production).

Below there is a script allowing to recreate the development database and fill in with data from the remote server.

MySQL

#!/bin/sh

DBUSER_REMOTE="root"
DBPASSWORD_REMOTE="root"
DBUSER_LOCAL="root"
DBPASSWORD_LOCAL="root"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "mysqldump -u$DBUSER_REMOTE -p$DBPASSWORD_REMOTE $DBNAME_REMOTE | gzip" | gunzip > $TMPFILE

mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL -f drop $DBNAME_LOCAL
mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL create $DBNAME_LOCAL
mysql -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL $DBNAME_LOCAL < $TMPFILE
rm $TMPFILE

PostgreSQL

#!/bin/sh

DBUSER_REMOTE="postgres"
DBUSER_LOCAL="postgres"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "pg_dump -U $DBUSER_REMOTE -Fc $DBNAME_REMOTE" > $TMPFILE

dropdb -U $DBUSER_LOCAL $DBNAME_LOCAL
createdb -U $DBUSER_LOCAL $DBNAME_LOCAL
pg_restore -U $DBUSER_LOCAL -Fc -d $DBNAME_LOCAL $TMPFILE
rm $TMPFILE

Sync media files

$HOST="www.example.com"
$PATH="/absolute/path/to/media"
$LOCAL_DIR="."
rsync -rltDvH $HOST:$PATH $LOCAL_DIR

PostgreSQL autostart on Mac OS X

I installed PostgreSQL 9.0 from „One Click Installer” and didn't want it to launch as system starts.

As PostgreSQL has been installed launchctl takes responsibility to take care of always running PostgreSQL daemon. /Library/LaunchDaemons/com.edb.launchd.postgresql-9.0.plist file sets up the daemon's behavior.

There are two options that can prevent the daemon after start up.

<key>Disabled</key>
<false/>

<key>RunAtLoad</key>
<true/>

„Disabled”=True prevents the job from loading at all and there will be not possible start the job even manually.
„RunAtLoad”=Fasle tells the launchctl not to run the job after loading.

Here we are, set „RunAtLoad” to False and the daemon won't be run automatically.

By the way, there are useful commands below to run and stop the daemon though:

sudo launchctl list | grep postgresql

display current daemon's pid or exit code.

sudo launchctl start com.edb.launchd.postgresql-9.0

start the daemon

sudo launchctl stop com.edb.launchd.postgresql-9.0

and stop it

Adding a new user

To create a new Postgres user.
First, create a new Linux user:

sudo useradd -M -N username # (do not create hoMe dir; do not add to group "userName")
sudo passwd username # enter password 'username'

And add user with the same name to Postgres:

psql -h 127.0.0.1 -p 5432 -U username -W -d template1 # insert your hostname and port if different 

In psql:

CREATE USER username WITH PASSWORD 'username';
ALTER USER username CREATEDB; -- allow user to create databases 

Setting up Postgres database server in Ubuntu 9.10

Do all as the superuser.

apt-get install postgresql postgresql-client postgresql-contrib
# To reset password for postgres user
echo "ALTER USER postgres WITH PASSWORD 'postgres';" | sudo -u postgres psql template1
# reset password for system user
passwd postgres

In bottom of /etc/postgresql/8.4/main/pg_hba.conf file set as the following:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

Script to easily create a Postgres database

#! /bin/sh
if [ ${#} -ne 3 ]
then
 echo "Usage: $0 db_name username password"
 exit 2
fi

DBNAME=$1
USER=$2
PWD=$3

echo "
CREATE USER $USER WITH PASSWORD '$PWD';
CREATE DATABASE "$DBNAME"
    WITH OWNER "$USER"
    ENCODING 'UTF-8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = "template0";
GRANT ALL PRIVILEGES ON DATABASE $DBNAME to $USER;
" | psql -U postgres -d template1

or if you want just to exec it once:

echo "
CREATE USER $USER WITH PASSWORD '$PWD';
CREATE DATABASE "$DBNAME"
    WITH OWNER "$USER"
    ENCODING 'UTF-8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'

TEMPLATE = "template0";
GRANT ALL PRIVILEGES ON DATABASE $DBNAME to $USER;
" | sudo -u postgres psql -U postgres -d template1