Backing up your database

There has been some confusion about backing up your database on Osiris. To do so, first ssh to osiris. Here are the main points:
  1. From the command prompt, do the backup. (The command prompt looks like this: [jensen@Osiris ~]$ -- some people thought they needed to type the $)
    pg_dump yourdatabasename >somename.sql
    pg_dump -c yourdatabasename >cleanstart.sql
    Of course, use the name of your database (same as your login), the ">" means write the backup to a file -- use any name you want.
    The resulting file consists of a series of SQL statements, that will recreate your database as it existed at the time of the dump.
    The -c (clean) option is interesting.It causes drop table statements to be included, so that the database is reconstructed exactly as it was at the time of the dump, all modifications after that will be lost. Using the first form after an accidental deletion of data will restore what was deleted, and retain more recent changes (and also generate a lot of error messages as it trys to create new tables of the same name, and create duplicates of existing rows). But since the main purpose of a backup is to retain your data incase all is lost, either method will work, and you can add or remove the drop statements before doing a restore.

  2. At any later time, you can use this file in psql to restore your database, in case it has become corrupted. You start psql from the command line, and then use its \i command, followed by the name of your backup file (somename.sql in my example).
    Here is an example of use, where I luckily had just done a bakup
[jensen@Osiris ~]$ pg_dump -c queens >cleanqueens.sql

[jensen@Osiris ~]$ psql queens
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

queens=> delete from qlog;
DELETE 266
queens=> -- OMG!!!
queens=> \i cleanqueens.sql 
DROP TABLE
...
CREATE TABLE
... lots of messages as commands in the file are executed
GRANT
queens=> select count(*) from qlog;
 count 
-------
   266
(1 row)

queens=> -- WHEW! THAT WAS CLOSE

mysql

Works the same way. At the osiris command line, you would type:
 mysqldump databasename >somename.mysql
To restore it later, use mysql, and check your documentation how to get the mysql commands from a file.

Compressing, and other advanced stuff

The dump file is text, you can read and edit it, just line html and php files.
You can copy all your files, or entire directories, to some other computer, using methods you already know.
However, you can save a lot of space by compressing.
To compress one file in linux, use gzip, and gunzip to compress and decompress, respectively. By default, the other file is removed. I fould that cleanqueens.sql was 35K, while cleanqueens.sql.gz took only 5K.

You might want to save your dump in public_html, and then back up the entire directory. This can be done using tar, to creade a single "archive" file, and compress while you are at it. To do this, be in your home (not public_ html) and type the command:
tar -czf myweb.tar.gz public_html
This will create (c) a compressed(z) file (f) named myweb.tar.gz. It will have all the files and directory structure within public_html. You can recreate your public_html on any machine by using tar with the x (extract) option, or list the files with the t option. The list will look something like:
[jensen@Osiris ~]$ tar -tzf myweb.tar.gz
public_html/index.html
public_html/cookies.php
public_html/private/
public_html/private/.htaccess
public_html/private/index.html
public_html/private/hello.php
.....
To extract the whole archive, say on a new server, or after I had mistakenly deleted your files or account:
[jensen@Osiris ~]$ tar -xzf myweb.tar.gz