e.g.

sudo tcpdump -A -i eth0 port 80

The -A option says to print in ASCII, the rest is pretty obvious.

You can then rsync this folder over ssh from another server or something like that.

backupdb.sh

Run with ./backupdb.sh [databasename] - assuming you have access to the database from your user account.

#!/bin/bash
pg_dump -Fc $1 > /var/lib/pgsql/9.3/backups/$1_$(date +"%Y-%m-%d").dump
find /var/lib/pgsql/9.3/backups/* -mtime +7 -exec rm {} \;

This is a custom format archive which you can use multi-job concurrency to restore in parallel (useful for big databases), e.g.

pg_restore -d myrestoredb -j 8 mydb_2015-01-01.dump

Would restore the dump into myrestoredb with 8 concurrent processors.

Fortunately I found an nice OSS app that mostly solves the problem - Double Command

This app lives in the System Settings and let's you click a few checkboxes to remap the home/end keys, the command key (yay Ctrl+C / Ctrl+V for cut and paste!)

Yay!

iptables -F
iptables -I INPUT 1 -i lo -j ACCEPT
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A INPUT -p tcp --dport 80 -j ACCEPT
iptables -A INPUT -p tcp --dport 22 -j ACCEPT
iptables -A INPUT -p tcp --dport 443 -j ACCEPT
iptables -A INPUT -j DROP

Also a basic port 8080 reverse proxy setup for HTTP/HTTPS on nginx is handy too...

upstream app {
    #ip_hash;
    server localhost:8080;
}

server {
    listen 80;
    #rewrite ^(.*) https://$host$1 permanent;
    location / {
        proxy_pass http://app;
        proxy_redirect http:// https://;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   html;
    }
 }

server {

    listen 443;
    ssl on;
    ssl_certificate      /etc/nginx/myssl.crt;
    ssl_certificate_key  /etc/nginx/myssl.key;
    server_name  localhost;

    location / {
        proxy_pass http://app;
        proxy_redirect http:// https://;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   html;
    }
}

Most resources show you how to create certificates for this directly in SQL Server, but for the purpose of being able to manage configuration externally, I think it's better to be able to generate an x509 certificate using normal tools (e.g. OpenSSL) and import that into SQL Server.

There's two bits of information on this that are not very well publicised -

• The certificate itself must be in DER binary format to import correctly.
• The private key must be in Microsoft's proprietary "PVK" format.

If these requirements aren't met, then a you will get a cryptic:

Msg 15468, Level 16, State 6, Line 1
An error occurred during the generation of the certificate.

To generate the proprietary PVK file from a regular RSA private key generated in OpenSSL a 3rd party utility is required

For this example, we'll generate a key and self-signed certificate using OpenSSL and convert it to the correct format for SQL Server, and import the certificate.

1. Generate 2048 bit RSA key

   openssl genrsa -des3 -out sql.key 2048

2. Generate certificate signing request

   openssl req -new -key sql.key -out sql.csr

3. Sign key with itself for 20 years (!)

   openssl x509 -req -in sql.csr -days 7300 -signkey sql.key -out sql.pem

4. Convert to binary DER in sql.cer

   openssl x509 -in sql.pem -inform PEM -out sql.cer -outform DER

5. Use pvk utility from above to convert to Microsoft format

   pvk -in sql.key -out sql.pvk -topvk

6. Now in SQL Server:

create certificate mysqlcert
      from file = 'c:\temp\sql.crt'
      with private key 
        (file = 'c:\temp\sql.pvk', 
         encryption by password = 'password entered during key generation', 
         decryption by password = 'password entered in step above')