Mladen Petrović mladenp

Postgres Cheatsheet

This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, "". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.

Setup

installation, Ubuntu

http://www.postgresql.org/download/linux/ubuntu/ https://help.ubuntu.com/community/PostgreSQL

PSQL

Magic words:

psql -U postgres

If run with -E flag, it will describe the underlaying queries of the \ commands (cool for learning!).

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

	it('componentDidMount should dispatch loadHomeAction', () => {
	const wrapper = shallow(<Home dispatch={jest.fn()} />);
	wrapper.instance().componentDidMount();
	expect(wrapper.instance().props.dispatch).toBeCalledWith(loadHomeAction());
	});

	/* Disable connection to Database */
	UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

	/* Get postgres process id */
	SELECT pg_backend_pid();

	/* Force Disconnect all clients ( use pid from previous step) */
	SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

	/* Finaly Drop database */

	Start Postgres without background service:
	pg_ctl -D /usr/local/var/postgres start

	Login to postgresql:
	psql -d mydb -U myuser -W
	psql -h myhost -d mydb -U myuser -W
	psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

	Default Admin Login:
	sudo -u postgres psql -U postgres

	var stripJSON = function(str) {
	return str.replace(/\\n/g, "\\n")
	.replace(/\\t/g, "\\t");
	};

	var jsonToObject = function(stringIn) {
	var data;
	try {
	data = JSON.parse(stripJSON(stringIn));
	console.log("json converted to object");

	find . -print0 \| while IFS= read -r -d '' file
	do
	echo "$file"
	done

	var degrees2meters = function(lon,lat) {
	var x = lon * 20037508.34 / 180;
	var y = Math.log(Math.tan((90 + lat) * Math.PI / 360)) / (Math.PI / 180);
	y = y * 20037508.34 / 180;
	return [x, y]
	}

	lon= -77.035974
	lat = 38.898717