Tutorials

Backup and Restore a PostgreSQL DB

Back up a database
pg_dump database_name > outfile

Back up a database and remove ownership from tables etc.
pg_dump -O -U user_name database_name > outfile

Restore a database (create new database and read in tables)
createdb -U user_name new_database_name
psql -U user_name -f infile new_database_name

Mount a second hard drive in Ubuntu

To find out the name of the new hard drive (assume sdb1 for this example):
sudo fdisk -l

Choose a path and name for the mount point and create it
sudo mkdir /media/mount_point_name

Change permissions
sudo chmod 777 /media/mount_point_name

To mount the hard drive only once
sudo mount /dev/sdb1 /media/mount_point_name

To automatically mount on startup, edit the fstab file. In this example the format is fat32(vfat). Change this value according to the format of the partition, e.g. ext3.
sudo gedit /etc/fstab
add /dev/sdb1 /media/backup vfat defaults 0 0

Reload the fstab file
sudo mount -a

MySQL on Ubuntu 9.10 with JDBC support

Installation:

Install MySQL

libmysql-java is only needed for JDBC support
sudo apt-get install mysql-server mysql-client libmysql-java

Set the root password. If this gives you an error (likely), then reset the root password according to the next section.
mysqladmin -u root password pw
mysql -u root -p

Reset the root password

Note that anyone with sudo access to your system can reset the root password.

sudo /etc/init.d/mysql stop
sudo /usr/bin/mysqld_safe --skip-grant-tables &
sudo mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD('new_pw') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Set the classpath for JDBC

This will set the classpath for all users on the system
sudo gedit /etc/environment

Add the following line
CLASSPATH=".:/usr/share/java/mysql.jar"
Log out and back in

Basic Usage

Log into mysql as root
mysql -u root -p

Create a new user with a password
mysql> CREATE 'User' user_name IDENTIFIED BY 'pw';

Create a new database
mysql> CREATE database db_name;

Grant a user privileges to access a database
mysql GRANT ALL PRIVILEGES ON db_name TO user_name@localhost IDENTIFIED by "pw";

Log in as a user
mysql -u user_name -p

Log in as a user on localhost
mysql -u user_name -h 127.0.0.1 -p

PostgreSQL 8.3 and PostGIS 1.3.5 on Ubuntu 9.10 (Karmic Koala)

Installation:

Install PostgreSQL

sudo apt-get install postgresql-8.3

Install PostGis

sudo apt-get install postgis

Install the graphical administration tool pgadmin (optional)

sudo apt-get install pgadmin3

Basic setup

The default user "postgres" has been without a password. The following steps will reset the password:
sudo su postgres -c psql
=# ALTER USER postgres with PASSWORD 'new password'
=# \q

Also change the password of the Unix user "postgres"
sudo passwd -d postgres
sudo su postgres -c passwd

To allow local users access to the server without the need of having the same unix user name, the following changes to the configuration file need to be made:
sudo gedit /etc/postgresql/8.3/main/pg_hba.conf
# local all all ident sameuser
local all all md5
sudo /etc/init.d/postgresql-8.3 restart

Basic Usage

Create a user with a password
sudo -u postgres createuser -P user_name

Create a database with owner O. If -O is not specified the user creating the DB is the default owner
sudo -u postgres createdb -O user_name db_name

When creating the database as a "regular" user with the user as the owner, the following is sufficient
createdb db_name

Delete a database
dropdb -U user_name db_name

Delete a user
sudo -u postgres dropuser user_name

Log in as a user to a database. If the -U option is not specified, the user name is assumed to be the same as the current unix user.
psql -U user_name -d db_name