Posts

Posted on

PostgreSQL Backup script (python)

Perhaps the following will be of use to others. In a nutshell, it’s a python script which backs up a provided list of PostgreSQL databases. I’ve written it for Windows, but it should work on Linux too (just change the paths in the BACKUP_DIR and dumper variables. No doubt it could be changed to query PostgreSQL for a list of databases, and dump these individually (like the MySQL python dumping script I wrote some time ago), but for now… let’s just stick with something simple.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#!python
 
from time import gmtime, strftime
import subprocess
import os
import glob
import time
 
# change these as appropriate for your platform/environment :
USER = "postgres"
PASS = "postgres"
HOST = "localhost"
 
BACKUP_DIR = "e:\\postgresql_backups\\"
dumper = """ "c:\\program files\\postgresql\\8.1\\bin\\pg_dump" -U %s -Z 9 -f %s -F c %s  """                  
 
def log(string):
    print time.strftime("%Y-%m-%d-%H-%M-%S", time.gmtime()) + ": " + str(string)
 
# Change the value in brackets to keep more/fewer files. time.time() returns seconds since 1970...
# currently set to 2 days ago from when this script starts to run.
 
x_days_ago = time.time() - ( 60 * 60 * 24 * 2 )
 
os.putenv('PGPASSWORD', PASS)
 
database_list = subprocess.Popen('echo "select datname from pg_database" | psql -t -U %s -h %s template1' % (USER,HOST) , shell=True, stdout=subprocess.PIPE).stdout.readlines()
 
# Delete old backup files first.
for database_name in database_list :
    database_name = database_name.strip()
    if database_name == '':
        continue
 
    glob_list = glob.glob(BACKUP_DIR + database_name + '*' + '.pgdump')
    for file in glob_list:
        file_info = os.stat(file)
        if file_info.st_ctime < x_days_ago:
            log("Unlink: %s" % file)
            os.unlink(file)
        else:
            log("Keeping : %s" % file)
 
log("Backup files older than %s deleted." % time.strftime('%c', time.gmtime(x_days_ago)))
 
# Now perform the backup.
for database_name in database_list :
    log("dump started for %s" % database_name)
    thetime = str(strftime("%Y-%m-%d-%H-%M"))
    file_name = database_name + '_' + thetime + ".sql.pgdump"
    #Run the pg_dump command to the right directory
    command = dumper % (USER,  BACKUP_DIR + file_name, database_name)
    log(command)
    subprocess.call(command,shell = True)
    log("%s dump finished" % database_name)
 
log("Backup job complete.")

That’s all folks.

Posted on

yum changelog (Want to know what you’re about to upgrade on CentOS/RHEL?)

Want to see what changes you’re about to apply when doing a ‘yum update’ ? Similar-ish to how ‘apt-listchanges’ works…

On CentOS 5.6, try :

  • yum install yum-changelog python-dateutil

Note, python-dateutil seems to be an unmarked dependency – i.e. you get an error message like : “Dateutil module not available, so can’t parse dates” when trying to run ‘yum changelog all updates’.

Note, /etc/yum/pluginconf.d/changelog.conf (but this didn’t seem to need changing to me).

Now you can do :

  • yum changelog all updates
  • yum changelog all mysql-server (or whatever package you’re interested in).

 

Posted on

Useful settings for history recording in bash (/etc/profile or ~/.bashrc)

?
1
2
3
4
5
shopt -s histappend
shopt -s checkwinsize
export HISTCONTROL=ignoredups:ignorespace
export HISTTIMEFORMAT='%Y-%m-%d %H:%M:%S  '
export EDITOR=vim

histappend: don’t overwrite .bash_history files on each logout; then when someone logs into the server, and messes something up, there’s a vague chance you’ll see what they did. Your history file will obviously grow to be quite big – but suppression of duplicates helps. Mine’s only 900kb after 7 months.

checkwinsize: check the window size after each command, might help some braindead programs cope with you resizing their windows, I guess.

HISTCONTROL: suppress duplicates, ignore spaces

HISTTIMEFORMAT: record a timestamp against each history entry; run ‘history’ to see an example of it’s output…

EDITOR: why would you not use vim?

Posted on

Random MySQL performance tuning stuff

  1. If you’re using InnoDB, ensure innodb_buffer_pool_size is set to a decent value – I choose about 25% of physical memory… ideally this is larger than your dataset size, but obviously may not be posible, and the server may have to do other stuff….
  2. If you’re using InnoDB stop the O/S from also trying to cache ‘stuff’ in the buffer cache – using innodb_flush_method=O_DIRECT
  3. Download the MySQL Tuner perl script (wget -O http://mysqltuner.pl > mysqltuner.pl) and run (e.g. perl mysqltuner.pl –user root –pass blahblah; it might point out a few variables to change; node the ‘maximum possible memory usage – you don’t want this to exceed 50% for a normal LAMP server).
  4. I use something like the below to optimize all tables, beware this will cause MyISAM tables to lock up … so you really need to run it in a quiet period.
?
1
2
3
4
5
6
7
8
9
10
SQL="select concat(TABLE_SCHEMA, '.', TABLE_NAME) from
    information_schema.TABLES where TABLE_SCHEMA IN
    ('database1','database2', 'databaseN') and Data_free > 500
    AND Engine = 'MyISAM' "
 
for table in $(mysql --skip-column-names  --batch -u root -prahrah -e "$SQL")
do
    mysql --batch -u root -pxxxxx -e "optimize table $table"
    sleep 10
done

See also PHP Conference InnoDB talk and MySQL Tuner

Posted on

Magento not finding product images (solution)

I migrated a Magento instance from one host to another, and in the process earnt myself some free beer; everything went well, apart from some of the thumbnail images not appearing. The image URLs would look like :

/media/catalog/product/cache/1/image/5e06319eda06f020e43594a9c230972d/images/catalog/product/placeholder/image.jpg

Things tried which didn’t help :

  1. rebuilding various Magento image caches and so on (login as admin -> System -> Cache management).
  2. Check PHP’s memory limit… (512mb… shouldn’t be a problem!)
  3. Check permissions on the media directory (chown -R www-data media .. didn’t fix it)
  4. Check PHP has gd installed/enabled – yes.
  5. Disable suhosin (no change)
  6. Check PHP logs (nothing relevant)
  7. Check Apache logs (nothing relevant)
  8. Changing the product to use a different thumbnail etc image (didn’t help)

I already knew the original host didn’t have a case sensitive file system, and this was partially fixed this using mod_speling in Apache, but this obviously doesn’t help when e.g. PHP decides to read and resize an image file.

I then tried comparing the contents in media/catalog/product with what’s in the catalog_product_entity_varchar table. So, in my case :

select value from catalog_product_entity_varchar where value like '/F%' limit 1;
returned something like :

/f/i/file_4_1.jpg

And looking in the above mentioned directory, only showed a directory called F. Simple fix… :

mv F f

(Obviously check the table doesn’t make reference to images with /F in them….  in my case it didn’t.)

<<reload web browser; images all appear and everything works.>>

What sort of idiot created case insensitive filesystems… grr.

Still, at least people can now browse online for Kitchens …. and see the appropriate thumbnails.

Posted on

Getting a kvm serial console with grub2

I’ve a few kvm guest servers, which I’ve been accessing using vnc – but this is a bit of a pain (getting port forwarding setup etc). Host and guests run Debian Squeeze with Grub2 installed/in use.

So, here’s how to do the ‘virsh console ‘ thing …

  1. Edit /etc/default/grub, specify
    ?
    1
    2
    3
    4
    GRUB_TERMINAL=console
    GRUB_SERIAL_COMMAND="serial --unit=0 --speed=115200 --word=8 --parity=no --stop=1"
    GRUB_CMDLINE_LINUX_DEFAULT=""
    GRUB_CMDLINE_LINUX="text console=tty0 console=ttyS0,115200n8"
  2. Run update-grub
  3. Edit /etc/inittab and enable ttyS0 for logins.
  4. Reboot
  5. ‘virsh console servername’ on the kvm host.

The libvirt config files I have already have the appropriate bits in them –

?
1
2
<serial type='pty'><target port='0'/></serial>
<console type='pty'><target type='serial' port='0'/></console>
Posted on

fsck -y (or fsck yes…)

Tip for the day:

Edit /etc/default/rcS on Debian/Ubuntu servers, and set FSCKFIX=yes (default of no) so next time your server runs fsck at startup, and spends hours doing it to only moan when it finds an error, and tells you to waste more time by running fsck with ‘-y’ (to fix it).

Quite why fsck even asks me to say ‘yes’ to it’s “do you want to fix…?” questions is another matter. It really annoys me – it’s not like I have enough information to make any sort of informed choice – it’s not like I know the structure that’s present on the disk, in order to say “No! You’re wrong FSCK, inode xxxxx should be blahblahblah”. I can only say ‘yes’.

If only FSCKFIX=yes was the default setting….. I can’t imagine Windows or OSX ever asking someone to answer such questions.

Posted on

All in a day’s work…. (magento and putty)

Here’s a random summary of some vaguely interesting techie things today.

Magento Migration

When exporting a Magento website from a third party (e.g 1&1), you’ll need to :

  1. Copy all files across; the ‘var’ directory can probably be omitted as it’s full of cache like stuff. ‘media’ and ‘var’ look like they need to be writeable by the web server.
  2. Edit the app/etc/local.xml file to specify the new database settings
  3. Check the various .htaccess files; the one in the root directory will probably need tweaking.
  4. Dump the database, and ensure that it has the following at the top of the dump file before restoring on the new host:
  • SET NAMES “utf8”
  • SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”

Failure to do the ‘utf8’ one, will lead to £ symbols being slightly wrong.

Failure to do the second will ensure that all the foreign key references fail (so restoration will be hard to start with) and if you disable the foreign key checks (‘SET FOREIGN_KEY_CHECKS=0;’) you’ll find that Magento will b0rk on access – complaining about nulls and so on in parts of the configuration (the error message will likely mention app/code/core/Mage/Core/Model/Mysql4/Config.php ).

All of the above is ignoring the fact that the Magento instance I migrated was infested with some malware, and I’m not yet sure how it ‘got there’, though I shot an email over to my buddy at Bing (Bing Digital is a leading Magento agency run by my buddy of 15 years). However, when it does next get infected, which it is likely to, I will at least be able to see what’s changed thanks to git.

Putty on OSX

Well, it sort of exists; if you use ‘brew‘ – namely ‘brew install putty’ does something – but doesn’t (yet? can it?) build the GUI. However, all I needed was to convert a putty ssh keyfile (blahblah.putty.key) into an openssh variant, for which puttygen works perfectly :

  • puttygen blahblah.putty.key -O private-openssh -o test.key

Strangely there don’t seem to be any free apps on the app-store for SSH. I’m almost surprised no one hasn’t just compiled putty and put it on there for 50p or free.