MySQL and Postgres command equivalents

Task: list existing databases, connect to one of the databases, then list existing tables and finally show the structure of one of the tables.

In Mysql:

show databases;
use database;
show tables;
describe table;
exit

In PostgreSQL:

\l
\c database
\dt
\d+ table
\q

PostgreSQL cluster using DRBD and hot standby

Cluster Configuration:

First install all the necessary packages:

yum install gfs2-utils cman fence-virtd-checkpoint lvm2-cluster perl-Net-Telnet rgmanager device-mapper-multipath ipvsadm piranha luci modcluster cluster-snmp ricci

yum groupinstall "High Availability"
yum install postgresql-server

chkconfig --level 123456 ricci on
chkconfig --level 123456 luci on
chkconfig --level 123456 cman on
chkconfig --level 123456 iptables off
chkconfig --level 123456 ip6tables off
chkconfig postgresql on
chkconfig cman on
chkconfig rgmanager on

Now edit the cluster configuration file:

vi vi /etc/cluster/cluster.conf

Make it look like this:

<?xml version="1.0"?>
<cluster config_version="7" name="pgcluster">
<clusternodes>
<clusternode name="10.39.30.7" votes="1" nodeid="1">
<fence/>
</clusternode>
<clusternode name="10.39.30.8" votes="1" nodeid="2">
<fence/>
</clusternode>
</clusternodes>
<rm>
<failoverdomains>
<failoverdomain name="PGSQL" nofailback="0" ordered="0" restricted="0">
<failoverdomainnode name="10.39.30.7"/>
<failoverdomainnode name="10.39.30.8"/>
</failoverdomain>
</failoverdomains>
<resources>
<ip address="10.39.30.6" monitor_link="on" sleeptime="10"/>
<postgres-8 config_file="/var/lib/pgsql/data/postgresql.conf" name="pgsql" shutdown_wait="5" />
</resources>
<service autostart="1" exclusive="0" domain="PGSQL" name="pgsql" recovery="relocate">
<drbd name="drdb-postgres" resource="r0">
<fs device="/dev/drbd0" fsid="6202" fstype="ext3" mountpoint="/var/lib/pgsql" name="pgsql" options="noatime"/>
</drbd>
<ip ref="10.39.30.6"/>
<postgres-8 ref="pgsql"/>
</service>
</rm>
<cman expected_votes="1" two_node="1"/>
<fence_daemon clean_start="1" post_fail_delay="0" post_join_delay="3"/>
</cluster>

DRDB Configuration:

Install the necessary files:

yum install gcc flex make libxslt rpm-build redhat-rpm-config kernel-devel

You need to download and install DRBD manually

wget http://oss.linbit.com/drbd/8.4/drbd-8.4.1.tar.gz 

 the following commands will generate DRBD RPM packages:

tar -xvf *.tar.gz
mkdir -p /root/rpmbuild/SOURCES/
cp drbd*.tar.gz /root/rpmbuild/SOURCES/
cd drbd-8.4.1
./configure --with-rgmanager --enable-spec --with-km
make tgz
rpmbuild --bb drbd.spec --without xen --without heartbeat --without udev --without pacemaker --with rgmanager
rpmbuild --bb drbd-kernel.spec
rpmbuild --bb drbd-km.spec

Now install the newly created packages:

cd /root/rpmbuild/RPMS/x86_64
rpm -i drbd-utils-8.4.1-1.el6.x86_64.rpm drbd-bash-completion-8.4.1-1.el6.x86_64.rpm drbd-8.4.1-1.el6.x86_64.rpm drbd-rgmanager-8.4.1-1.el6.x86_64.rpm drbd-km-2.6.32_279.14.1.el6.x86_64-8.4.1-1.el6.x86_64.rpm

Add your nodes IP addresses to the hosts file on both machines:

vi /etc/hosts

10.39.30.7 RHPG1
10.39.30.8 RHPG2

Create a DRBD configuration file:

vi /etc/drbd.d/r0.res

resource r0 {
   device /dev/drbd0;
   meta-disk internal;
   on RHPG1 {
      address 10.39.30.7:7789;
      disk /dev/sdb1;
   }
   on RHPG2 {
      address 10.39.30.8:7789;
      disk /dev/sdb1;
   }
}

Create the partion /dev/sdb1 but do not format it:

fdisk /dev/sdb

Run on both machines:

drbdadm create-md r0
modprobe drbd
drbdadm up r0

Run on one of the machines to create the file system:

drbdadm -- --overwrite-data-of-peer primary r0

Check the sync status on any of the hosts with:

service drbd status

Create the file system on /dev/drbd0

mkfs.ext3 /dev/drbd0

and move over the PostgreSQL data

mkdir /tmp/pgdata
mount /dev/drbd0 /tmp/pgdata
cp -r /var/lib/pgsql /tmp/pgdata

Wait until the data is synced over the two hosts check the status with:

service drbd status

Unmount the drbd device:

umount /dev/drbd0

and then, on both hosts do:

rm -rf /var/lib/pgsql/*

Restart the drbd service

service drbd restart

the status from:

service drbd status

should show that both hosts are in secondary:

0:r0 Connected Secondary/Secondary UpToDate/UpToDate C

And ready to be managed by rgmanager.

Drop all tables from a PostGreSQL DB

Replace dbname with the actual database name:

psql -U dbname -W -t -d dbname -c "SELECT 'DROP TABLE ' || n.nspname || '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)" > /tmp/droptablespsql -U dbname -W -d dbname -f /tmp/droptables

PostgreSQL dump and restore

Backup one database:

pg_dump dbname > outfile

or if you want to clean (drop) schema prior to create (drop databases prior to create):

pg_dump -c dbname > outfile 

Restore one database:

psql dbname < infile

Copy database from one host to another:

pg_dump -h host1 dbname | psql -h host2 dbname

Backup all databases:

pg_dumpall > outfile

Note: you can add the --clean option to drop tables before restoring

Restore all databases:

psql -f infile postgres

PostgreSql - Create user and DB

To create a user:

createuser -d -P -l username

To create a database:

createdb -W -U username dbname

Test with:

psql -d dbname -U username -W

if you get an error message saying "psql: FATAL: Ident authentication failed for user 'username'"

vi /etc/postgresql/8.4/main/pg_hba.conf

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:

local all all trust
host all all 127.0.0.1/32 trust

for any host:

local all all trust
host all all 0.0.0.0/0 trust

You will have to restart the service

/etc/init.d/postgresql-8.4 restart

NOTE: If you want the server to prompt for a password instead of trust use password but this method sends the password in cleartext over the network.