Tuesday 19 November 2013

Helpful Postgres Statements


 Find to 20 largest tables in the database


 SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  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 20;

Drop existing connections for a particular database

This will drop existing connections:
Query pg_stat_activity and get the pid values you want to kill and issue select pg_terminate_backend(pid int) to them.

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

Creating a user to access a server

The idea is to create a user and assign the user with permissions to access a single database from a particular IP source. They will be able to access on the port set out.
  • set up user in postgres (using super user account)
CREATE USER user234 WITH PASSWORD '3f5ffg#' VALID UNTIL '2014-04-01';
GRANT CONNECT ON DATABASE mydb TO user234;
GRANT SELECT ON table23 TO user234;
GRANT SELECT ON table24 TO user234;
GRANT SELECT ON table25 TO user234;
GRANT SELECT ON ALL TABLES TO user234; -- bah.. just give permission to all 
  • edit # vi /var/lib/pgsql/9.1/data/pg_hba.conf
host    mydb   user234            172.23.20.32/24         md5
  • Reload conf as postgres user
/usr/pgsql-9.1/bin/pg_ctl reload -D /var/lib/pgsql/9.1/data/

Datetime Setting

The problem lies because of the date/time field settings..

to fix this we can first show the datestyle in the database..

=> SHOW datestyle;
 DateStyle
-----------
 ISO, YMD
(1 row)

Then we can set the datestyle to the correct style..


=> SET datestyle = "ISO, DMY";
SET
=> SHOW datestyle;
 DateStyle
-----------
 ISO, DMY
(1 row)

Restart Postgres

/etc/init.d/postgresql restart

OR


service postgresql restart

Set the timezone in a PSQL DB

=> SET TIME ZONE 'Australia/Victoria';
SET
=> SHOW TIME ZONE;
      TimeZone
--------------------
 Australia/Victoria
(1 row)

PSQL commands to file

psql -U user234 -d database -H -c "select * from ...;" -o /home/me/file.html

Database Backup

Simple backup which is compressed.

pg_dump -U user234 mydb | gzip > /home/me/backups/mydb_YYYMMDD.sql.gz

exclude a table


pg_dump -U user234 --exclude-table=tabletoexclude mydb | gzip > /home/me/backups/mydb_YYYMMDD.sql.gz

Schema Only

pg_dump -U user234 --schema-only -f mydb_backup_schema_YYYMMDD.sql mydb

Database Restore

Restore from the backup made

psql -U postgres
DROP DATABASE mydb; CREATE DATABASE mydb OWNER user234;\q
gunzip -c /home/me/backups/mydb_YYYMMDD.sql.gz | psql -U user234 mydb;

Database Clone

Clone the database, an easy way to set up a clone to test with.

CREATE DATABASE newdb2 WITH TEMPLATE mydb;

The following will drop the database and copy back the original one. This doesn't really follow with the previous statements, so read carefully.

DROP DATABASE mydb;
CREATE DATABASE mydb WITH TEMPLATE mydb_original;
ALTER DATABASE mydb OWNER TO user234;





Thursday 7 November 2013

Downgrading / Switching openJDK and other Java versions in Ubuntu

If you are running openJDK or other versions and need to develop or use Java in an older version there is an easy way to switch working versions.

First confirm your current version;

root# java -version 
java version "1.7.0_25" 
OpenJDK Runtime Environment (IcedTea 2.3.10) (7u25-2.3.10-1ubuntu0.13.04.2) 
OpenJDK 64-Bit Server VM (build 23.7-b01, mixed mode) 

Note: This way assumes that you have upgraded but not removed the older version. If you only have the newer version then first install the older version you want to downgrade to then you can simply switch.

The command you should run is:

root# update-alternatives --config java 

Upon running you will receive a similar screen and switching to another version is as easy as typing a number.

There are 3 choices for the alternative java (providing /usr/bin/java). 

Selection Path Priority Status 
------------------------------------------------------------ 
* 0 /usr/lib/jvm/java-7-openjdk-amd64/jre/bin/java 1071 auto mode
 1 /usr/lib/jvm/java-6-openjdk-amd64/jre/bin/java 1061 manual mode
 2 /usr/lib/jvm/java-6-sun/jre/bin/java 63 manual mode
 3 /usr/lib/jvm/java-7-openjdk-amd64/jre/bin/java 1071 manual mode

Press enter to keep the current choice[*], or type selection number: 1 
update-alternatives: using /usr/lib/jvm/java-6-openjdk-amd64/jre/bin/java to provide /usr/bin/java (java) in manual mode 

You can then check your version again;

root# java -version 
java version "1.6.0_27" 
OpenJDK Runtime Environment (IcedTea6 1.12.6) (6b27-1.12.6-1ubuntu0.13.04.2) 
OpenJDK 64-Bit Server VM (build 20.0-b12, mixed mode)

Tuesday 22 October 2013

Building mupdf on CentOs

So you want to know how to compile MuPDF on CentOs, ScientificLinux or Redhat? Let's jump in!

Setup

  1. log in as root or otherwise set the right permissions and use 'sudo' for make install
  2. run yum install libtool
  3. run mkdir mupdf ; cd mupdf
  4. run wget http://mupdf.googlecode.com/files/mupdf-1.1-source.tar.gz
  5. run wget http://mupdf.googlecode.com/files/mupdf-thirdparty-2012-08-14.zip
  6. run unzip -a mupdf-thirdparty-2012-08-14.zip use -a or you will have ^M issues
  7. run tar -xvf mupdf-1.1-source.tar.gz

Compile Third party libs

# cd thirdparty/freetype-2.4.10
# chmod -R 775 *
# sh autogen.sh
# ./configure
# make; make install

# cd thirdparty/jbig2dec
# chmod -R 775 *
# sh autogen.sh
# ./configure
# make; make install

# cd thirdparty/jpeg-9
# chmod -R 775 *
# ./configure
# make; make install

# cd thirdparty/openjpeg-1.5.0-patched
# chmod -R 775 *
# ./configure
# make; make install

# cd thirdparty/zlib-1.2.7
# chmod -R 775 *
# ./configure
# make; make install


Patch and Compile main mupdf source

  • yum install fontconfig fontconfig-devel freetype freetype-devel
  • yum install libX11 libX11-devel libXext libXext-devel
  • cp /usr/local/lib/* /usr/lib/
  • if something doesnt work try to yum install libtools-devel and run ldconfig
  • run ldconfig
  • Next:
    # cd mupdf/mupdf-1.1-source
    # chown -R root.root *
    # chmod -R 775 *
    # make; make install
    

Notes

  • If the directories are different (i.e. /usr/bin and not /usr/local/bin ) then ln -s mudraw, mupdf and mubusy to the correct dir and dont forget the man pages in /usr/local/share/man/man1

Updates

  • [2015-10-09] MuPDF is up to version 1.7a and it may or may not need the third-party  libs. Check out the download directory here.

Tuesday 14 May 2013

Backup btrfs with an external HDD



Ever since my old HDD started dying and filling up with bad sectors I have been thinking about a solid backup solution for my system. 

I have;
  • 1 internal HDD (btrfs)
  • 1 external HDD (btrfs)
  • Arch Linux

The Problem


How can we fully backup our system?

Discussion


There are 2 types of backup.

One type is where the data is protected from failure of the primary medium. This is where the data is typically backed up to another storage medium like an external drive. Examples include the disk dying or being stolen.

The other type is protection against accidental deletion and corruption of data. Think Apple 'timemachine' or a snapshot of your data at a particular time.

They are different. Let's explain the 'timemachine' concept with one example; you may have a corrupted file and your backup program also backed up the corrupted file, so you cannot recover the file from your normal backup. Instead, if you have a snapshot of how your data looked at a certain time you could restore the file from an earlier time where maybe it wasn't corrupted.

BTRFS


Introducing btrfs. A relatively unstable and heavily in-development File System.

One feature over other File Systems (FS) is that this FS allows for snapshots-in-time, effectively letting you restore files as they were when you made the snapshot. The great thing about it is that it doesn't actually take up much space because it just records the changes made to files.

The Solution


Let's create snapshots and backup our data in full. This should give us a full backup solution covering both types of backup. We are protected if data is corrupted or destroyed and we are also protected in case of unintentional modifications or deletions.


We can create a script of which there are 3 components;

  • a local snapshot
  • an rsync backup
  • a remote snapshot

fstab


# backup drive - external USB-3
UUID=5ab08618-1e9d-4faf-a719-bafd19875f77  /mnt/buffalo  btrfs  defaults,noauto  0  0


The local snapshot

A snapshot on our system so we can restore files that were accidentally deleted or corrupt or whatever...

Rsync backup

Copy over all the files on our disk to the external backup drive in case the main drive fails or otherwise blows up.

Remote snapshot

Lets put snapshots on the backup drive as well.. this way if our main drive fails we will also have snapshots still when we recover the data.

It's important to note that remote snapshots only work if the remote backup is on a sub-volume. Because a snapshot can only be taken on a sub-volume.

The Code


The following script can be used as a backup solution which includes snapshots and redundancy.

#!/bin/sh

echo "Starting Backups"

DATE=`date +%F`

echo "$DATE Creating local snapshot.."

# btrfs subvolume snapshot [-r]  [/]
if [ ! -d /snapshots/$DATE ]
then 
 btrfs subvolume snapshot -r / /snapshots/$DATE
 echo "$DATE Done."
else
 echo "$DATE /snapshots/$DATE already exists."
fi

echo "$DATE Creating backup snapshot.."
if [ ! -d /snapshots/$DATE ]
then 
 btrfs subvolume snapshot -r /mnt/buffalo/arch_btrfs/ /mnt/buffalo/arch_btrfs/snapshots/$DATE
 echo "$DATE Done."
else
 echo "$DATE /mnt/buffalo/arch_btrfs/snapshots/$DATE already exists."
fi


echo "$DATE Backing up to external HDD.."

# Backup files on ext4 external disk
rsync -aAXv / /mnt/buffalo/arch_btrfs/ --exclude={/dev/*,/proc/*,/sys/*,/tmp/*,/run/*,/mnt/*,/media/*,/lost+found,/snapshots,/home/*/.gvfs} &> /var/log/rsync.log

echo "$DATE Done. See /var/log/rsync.log for more info."