Database Basics

This page contains basic information about the Database.

---

ER Diagram

Paths

Executing Queries

Optimizing Slow Queries

Database Tuning

Database Backup

Database Dump and Restore

Database Monitoring

Database IP Authorization

Moving the database to a new drive

---

ER Diagram

Full Resolution ER Diagram

• The .pdf and .vdx versions of the most recent ER diagram can be found here

• Note: by entering \d table_name; into psql, one can see the columns (including their type and modifiers), indexes, foreign key constraints, and references of any table in the database (replacing 'table_name' with a table of interest).

Paths

/db/pgsql/* is the database data directory which contains the data and configuration.

Executing Queries

As root enter su postgres -c "psql ops" to access the PSQL command-line interface. From here you can enter SQL directly on the database. (DO SO CAUTIOUSLY)

Enter \q to quit PSQL.

Optimizing Slow Queries

• Enter \timing to the psql interface to time all queries entered into psql.

• Use EXPLAIN ANALYZE in front of slow queries to identify slow components. See the documentation for more information.

• Submit the output of EXPLAIN ANALYZE to this website to make understanding the output easier.

• Review the PostgreSQL logs/reports to help identify slow queries.

Database Tuning

• Tuning Your PostgreSQL Serve (PostgreSQL Wiki)

• Review the PostgreSQL logs/reports

• PostgreSQL 9.0 High Performance is considered one of the leading texts on how to most effectively optimize PostgreSQL. This resource is available free through the KU library here.

Database Backup

The database data directory is backed up automatically every night by CReSIS IT. Note that this does not ensure transaction control and may result in corrupted data if the data directory is backed up during an active transaction.

Firstly, stop the server:

sudo -i

service httpd stop

service tomcat6 stop

service postgresql-9.3 stop

Then, contact IT to back up the folder and everything below that located at /cresis/snfs1/web/ops.

Finally, start the server:

service postgresql-9.3 start

service tomcat6 start

service httpd start

The current CReSIS OPS file system backup settings are:

<< / >>
                skip: tmp_mnt
                +skip: core
<< /cresis/snfs1 >>
                +skip: .
<< /tmp >>
                skip: .?* *
<< /export/swap >>
                swapasm: .
<< /nsr >>
                allow
<< /nsr/logs >>
                logasm: .
<< /var >>
                logasm: .
<< /usr/adm >>
                logasm: .
<< /usr/spool >>
                logasm: .
<< /usr/spool/mail >>
                mailasm: .
<< /usr/mail >>
                mailasm: .

Database Dump and Restore

See the documentation on SQL Dump, the pg_dump function, and the pg_restore function for more information.

• Dumping the database may be useful when migrating data to a new server, creating a static backup of all the data, and when replicating the production database in a development environment.

To dump the database, the following commands can be issued as root, substituting the /path/to/output/dir:

su postgres
pg_dump --data-only -Fd ops -f /path/to/output/dir

To restore the database from a dump, one should first clear the contents of the database (THIS IS IRREVERSIBLE AND WILL RESULT IN ALL CURRENT DATA BEING WIPED FROM THE DATABASE. DO THIS WITH CAUTION):

cd /var/django/ops
source /usr/bin/venv/bin/activate

python manage.py flush --no-initial-data

Next, the following commands can be issued, substituting the /path/to/dump/dir:

su postgres
pg_restore --data-only --disable-triggers --no-data-for-failed-tables -d ops /path/to/dump/dir

• Once the database has been restored, check that there were no warnings or errors and make sure the data looks correct. Note that warning/errors may occur for tables postgres manages automatically (tables that contain no CReSIS-specific OPS data, only metadata maintained by postgres internally).

• Issue a VACUUM ANALYZE after the database has been restored.

Database Monitoring

Need to expand this section to discuss ways to monitor all the different things going on and where connections/requests are coming from.

sudo -i
su postgres
psql ops
SELECT datname,usename,client_addr,waiting,query_start FROM pg_stat_activity;
 datname | usename  | client_addr | waiting |          query_start          
---------+----------+-------------+---------+-------------------------------
 ops     | admin    | 127.0.0.1   | f       | 2014-10-01 10:30:04.878004-05
 ops     | admin    | 127.0.0.1   | f       | 2014-10-01 10:29:48.931431-05
 ops     | postgres |             | f       | 2014-10-01 10:36:22.515665-05

Database IP Authorization

Database access is restricted to the localhost. Authentication is done through Django IP Authorization.

Moving the database to a new drive

To move the database to another drive, disable postgres, rename the data folder, mount the new drive where the data folder was, and then move the data back into the data folder on the new drive

service postgresql-12 stop  # Stop postgres
service postgresql-12 status  # Check to make sure it is stopped
mv /db /dbold  # Move old data dir
df -h
lsblk  # Find drive to format (e.g. /dev/sdc)
parted /dev/sdc mklabel gpt  # Format the drive
parted -a opt /dev/sdc mkpart primary ext4 0% 100%  # Partition the entire drive
mkfs.ext4 -L datapartition /dev/sdc1  # Create a filesystem on the partition
e2label /dev/sdc1 opsdatabase  # Relabel the partition

mount -o defaults /dev/sdc1 /db

Edit fstab file to automatically mount on startup

nano /etc/fstab

Add line:

LABEL=opsdatabase /db ext4 defaults 0 2

mv /dbold/* /db  # Move database files back to /db
service postgresql-12 start  # Restart postgresql service