Nesh P. NMItools

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.

Methods for archiving Postgres databases

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.

Moved

Now located at https://github.com/JeffPaine/beautiful_idiomatic_python.

Why it was moved

Github gists don't support Pull Requests or any notifications, which made it impossible for me to maintain this (surprisingly popular) gist with fixes, respond to comments and so on. In the interest of maintaining the quality of this resource for others, I've moved it to a proper repo. Cheers!

	#!/usr/bin/python3

	# pip3 install pandas matplotlib
	import pandas as pd
	from matplotlib import pyplot as plt

	# this gets the worldwide confirmed cases from JHU
	url = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv'

	# read/download csv

	#EXTM3U
	#EXTINF:-1,BBC - Radio 1
	http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio1_mf_p
	#EXTINF:-1,BBC - Radio 2
	http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio2_mf_p
	#EXTINF:-1,BBC - Radio 3
	http://open.live.bbc.co.uk/mediaselector/5/select/version/2.0/mediaset/http-icy-aac-lc-a/format/pls/vpid/bbc_radio_three.pls
	#EXTINF:-1,BBC - Radio 4
	http://bbcmedia.ic.llnwd.net/stream/bbcmedia_radio4fm_mf_p
	#EXTINF:-1,BBC - Radio 5 live

	/*
	Vincent Picavet <[email protected]>

	Work distributed under MIT Licence.

	Automated generation of edges geometries according to topology and nodes geometry

	A nodes table and a edges table design a network.
	Edges are straight lines between id_from and id_to nodes

	# Tom 17/05/2012

	# Example GeoAlchemy script.

	from sqlalchemy import create_engine, distinct, func, MetaData, Sequence
	from sqlalchemy.ext.declarative import declarative_base
	from sqlalchemy.orm import sessionmaker
	from geoalchemy2 import Geometry

	# Database connection

	/*
	Taken and cribbed from blog.datalicious.com/free-download-all-australian-postcodes-geocod

	May contain errors where latitude and longitude are off. Use at own non-validated risk.
	*/

	SET NAMES utf8;
	SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

	DROP TABLE IF EXISTS postcodes_geo;