DEV Community: Sambhav Jain

Backup Postgres to S3

Sambhav Jain — Wed, 06 Jan 2021 10:42:03 +0000

This article describes an easy way to backup a Postgres database to Amazon S3 using s3cmd and crontab in Linux environment.

Install S3cmd

S3cmd is a command-line utility for managing data in AWS S3.

For Debian/Ubuntu

apt-get install s3cmd

For RedHat/CentOS

yum install s3cmd

For OSX

brew install s3cmd

Set Amazon access id & secret keys using the --configure option. Enable encryption for data transferring as well as HTTPS.

s3cmd --configure

Backup the database

pg_dump -v --format=c -h localhost -U YOUR_USER YOUR_DB > backup.dump

Push to S3

s3cmd put backup.dump s3://YOUR_BUCKET_NAME --encrypt

Automate the process

Create a new bash file in the root directory.

#!/usr/bin/env bash

DB_NAME=$1
DB_USER=$2
DB_PASS=$3

BUCKET_NAME=<YOUR_NAME_HERE>

TIMESTAMP=$(date +%F_%T | tr ':' '-')
TEMP_FILE=$(mktemp tmp.XXXXXXXXXX)
S3_FILE="s3://$BUCKET_NAME/backup-$TIMESTAMP"

PGPASSWORD=$DB_PASS pg_dump -Fc --no-acl -h localhost -U $DB_USER $DB_NAME > $TEMP_FILE
s3cmd put $TEMP_FILE $S3_FILE --encrypt
rm "$TEMP_FILE"

Change file permission to be executable using chmod +x and test the file using:

./FILE_NAME.sh DATABASE_NAME USER_NAME DATABASE_PASSWORD

It should upload the backup to the S3 configured earlier.

Attach to a cron job

crontab -e

Edit the file to execute the script at (say) every sunday at midnight:

0 0 * * 0 /home/ubuntu/FILE_NAME.sh DATABASE_NAME USER_NAME DATABASE_PASSWORD

Save the file and check the cron logs in /var/log/syslog file.

Sort by timestamps (month and year) in PostgreSQL

Sambhav Jain — Tue, 05 Jan 2021 12:01:27 +0000

Let's understand how we can sort by a non-timezone bound timestamp value, here we will call it timestamp_x in table dev_to;

select to_char("timestamp_x", 'Month YYYY') as "month", to_char("timestamp_x", 'YYYYMM') as "monthN" from dev_to
order by to_char("timestamp_x", 'YYYYMM');

Let's break the query is smaller chunks and understand whats happening:

to_char("timestamp_x", 'Month YYYY') will convert timestamp_x into its Month and Year form, for example: January 2021.
to_char("timestamp_x", 'YYYYMM') will again convert timestamp_x into its Month and Year form, but this time it will be numeric, for example: 202101.
order by to_char("timestamp_x", 'YYYYMM') will order the data by year and month (in that order as well), for example:

January 2021, August 2019, March 2020

will be sorted as:

August 2019, March 2020, January 2021

Hence, the final output will be sorted by both month and year as desired.

Cloning source code of a docker image

Sambhav Jain — Fri, 18 Dec 2020 17:08:53 +0000

We all know that docker is a great tool to build and deploy applications. However, it is rather difficult to get the source code of a 3rd party image as a developer. In this guide, I will attempt to clone the source code of a downloaded image from a remote server.

There are some things to keep in check in case you come across such:

Inspecting a docker image:

sudo docker image inspect <IMAGE_NAME>

This command will provide the image configuration in JSON with one crucial detail to look for i.e., where the actual code of the image resides within it. Look out for WorkingDir under ContainerConfig and that will be the root of the source code being run by the image.

For Example:

[
    {
        "Id": "...",
        "RepoTags": [
            "..."
        ],
        "RepoDigests": [
            "..."
        ],
        "Parent": "",
        "Comment": "",
        "Created": "2020-11-04T16:28:13.6789081Z",
        "Container": "...",
        "ContainerConfig": {
            "Hostname": "cd849c8982e0",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "NODE_VERSION=14.15.0",
                "YARN_VERSION=1.22.5",
                "NODE_ENV=production"
            ],
            "Cmd": [
                "/bin/sh",
                "-c",
                "#(nop) ",
                "CMD [\"node\" \"/var/apps/app-name/main.js\"]"
            ],
            "Image": "...",
            "Volumes": null,
            "WorkingDir": "/var/apps/app-name", // <--- THIS LINE
            "Entrypoint": [
                "docker-entrypoint.sh"
            ],
            "OnBuild": null,
            "Labels": {}
        },
        "DockerVersion": "19.03.12",
        "Author": "",
        "Config": {
            "Hostname": "",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "NODE_VERSION=14.15.0",
                "YARN_VERSION=1.22.5",
                "NODE_ENV=production"
            ],
            "Cmd": [
                "node",
                "/var/apps/app-name/main.js"
            ],
            "Image": "...",
            "Volumes": null,
            "WorkingDir": "/var/apps/app-name",
            "Entrypoint": [
                "docker-entrypoint.sh"
            ],
            "OnBuild": null,
            "Labels": null
        },
        "Architecture": "amd64",
        "Os": "linux",
        "Size": 932787935,
        "VirtualSize": 932787935,
        "GraphDriver": {
            "Data": {
                "LowerDir": "...",
                "MergedDir": "...",
                "UpperDir": "...",
                "WorkDir": "..."
            },
            "Name": "overlay2"
        },
        "RootFS": {
            "Type": "layers",
            "Layers": [
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "...",
                "..."
            ]
        },
        "Metadata": {
            "LastTagTime": "0001-01-01T00:00:00Z"
        }
    }
]

Cloning a docker image

# Create a new temporary image from the existing docker image
$ sudo docker create --name="tmp_$$" <IMAGE_NAME:TAG>

# Export the temporary image to a tarball
$ sudo docker export tmp_$$ > image.tar

# Delete the temporary image
$ sudo docker rm tmp_$$

# Let's use sftp to download the tarball to our local machine
$ sftp -i <KEY> <USERNAME>@<SERVER_NAME>
$ get image.tar

# Now once you have the tarball downloaded to you local machine, extract and navigate to the `WorkingDir` highlighted earlier, which here was `/var/apps/app-name/`
$ cd /Downloads/<EXTRACTED_IMAGE_FOLDER_NAME><WorkingDir>

And there you have it, the source code of a 3rd party image.

Things to consider before attempting this:

sftp will use your local bandwidth to download any files from the remote server, so a Wi-Fi/Broadband connection is suggested for this as the docker images tend to be rather big in size and will take a considerable amount to download, therefore.
Add sudo before the docker commands in case without sudoer it doesn't work.