Tomas Holderness tomasholderness

PostgreSQL Database Archiving & Backup

Postgres includes the command line tools pg_dump and pg_restore to store a copy of a database as a file on disk.

Use pg_dump/pg_restore to create binary files in the native postgres dump format

this method is efficient, but because the resulting .backup file is binary it can't be edited once it is created which can lead to problems when restoring. Read more: https://www.postgresql.org/docs/current/static/app-pgdump.html
this is the preffed method if working locally, and you have no reason to edit the backup file
if working with a cloud hosted service such as AWS RDS then see option (2) below. AWS uses specialist permissions and usernames which are typically are not available on a local system and so it is helpful to be able to change these in the backup file prior to restoring.

Use pg_dump to create plain SQL files containing database structure and data as text.

	import json
	import rasterio


	# open file as a global
	SRC = rasterio.open('s3://bucket/cog.tif') # <<-- THIS LINE HAS MOVED UP FROM THE FUNCTION BELOW :-)

	def handler(event, context):
	"""Lambda function to read pixel values from a COG"""

	import json
	import rasterio

	def handler(event, context):
	"""Lambda function to read pixel values from a COG"""

	# open file
	src = rasterio.open('s3://bucket/cog.tif')

	# note: lat, lon extracted from Lambda event object

	# Add required overalys
	gdaladdo input.tif 2 4 8 16
	# Create the COG
	gdal_translate input.tif cog.tif -co TILED=YES -co COPY_SRC_OVERVIEWS=YES -co COMPRESS=LZW -co BIGTIFF=YES

	// Library
	var jwkToPem = require('jwk-to-pem');

	// Paste your JWK here
	var key='{"alg":"RS256","e":"","kid":"","kty":"RSA","n":"","use":"sig"}';

	// Parse the key
	key = JSON.parse(key);

	// Convert to RSA Public Key (.pem)

	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="UTF-8">
	<script src="https://code.jquery.com/jquery-2.1.4.js"></script>
	<script src="jquery.countdown-2.1.0/jquery.countdown.js"></script>
	<script src="http://momentjs.com/downloads/moment.js"></script>
	<script src="http://momentjs.com/downloads/moment-timezone.js"></script>
	</head>
	<body>

	ALTER TABLE all_reports ADD COLUMN status character varying DEFAULT 'confirmed';
	COMMENT ON COLUMN all_reports.status IS '{character varying} Status of the report (defaults to confirmed)';