New Ubuntu EC2 Instance things to remember

if you want to attach an existing EBS volume to the instance, make sure it's in the same Availability Zone (like "us-east-1c")

To SSH at all:
download that private key that they make you create as part of the setup console
ssh -i (thatkey.pem) -l ubuntu
(there's one user created at the start, called ubuntu)
sudo adduser dantasse
sudo vim /etc/group # and add dantasse to sudo group
sudo vim /etc/ssh/sshd_config # and set PasswordAuthentication to yes
sudo /etc/init.d/ssh reload
Now you should be able to SSH.

Attach the EBS volume on the AWS console. For example, attach it as /dev/sdf. Because it's Ubuntu, it will be attached as /dev/xvdf instead. (because reasons :P) You have to mount it still, though. Say you want to mount it at /data:
sudo mkdir /data
sudo mount /dev/xvdf /data

Some things I learned in that adventure that I always forget

dpkg --get-selections | grep -v deinstall 
shows all packages you have installed on Ubuntu

/etc/apt/sources.list is where your repositories (for debian/etc packages) are stored

lsb_release -a
shows what Ubuntu release you're running

A hairy adventure in upgrading Ubuntu and Postgres

(Detailing this more as a historical artifact than anything else, but it might help someone.)

I had an EC2 Ubuntu 14.04 Trusty machine running PostgreSQL 9.3 and PostGIS 2.1 that I had to upgrade. For whatever reason.
sudo do-release-upgrade

but PostGIS was holding it back. I don't remember how I found this out but I think it was a simple google. So I did:
sudo apt-get remove postgresql-9.3-postgis-2.1 # turns out this was a mistake
sudo do-release-upgrade

ok, now I'm on Ubuntu 16.04 Xenial. Now to upgrade postgres:
sudo pg_upgradecluster 9.3 main /data/db/postgresql/9.5/main

ugh but wait, can't do it without PostGIS. hold on --
psql tweet
tweet=# select * from tweet_pgh limit 1;
(some kind of error because I don't have PostGIS installed anymore.)

Huh.
psql tweet
tweet=# ALTER EXTENSION postgis UPDATE TO "2.3.1";
(some kind of error about "no update path from 2.1.2 to 2.3.1")

Huh. I guess I have to 1. get PostGIS back, 2. give it an "update path" to 2.3.1.
Getting PostGIS back:
sudo apt-get install postgresql-9.3-postgis-2.1
(not found)

Huh. I flailed here for a while and then downloaded 2.1.2 from source, and did the old
./configure
make
make install
But of course it wasn't that easy! Got one compile error about json and using this old ticket, realized I had to go into liblwgeom/lwin_geojson.c and edit "#include " to "#include "
Got another compile error and had to like #IFDEF out references to AggState and WindowAggState in lwgeom_accum.c (as in this old ticket) and assume it'll work (sure did). Or rather, I was able to configure and make it, then I just symlinked it into the right place:
sudo ln -s /home/dantasse/postgis-2.1.2/postgis/postgis-2.1.so /usr/lib/postgresql/9.3/lib 

THEN, I could do this:
tweet=# select * from tweet_pgh limit 1;

but I couldn't do this:
tweet=# ALTER EXTENSION postgis UPDATE TO "2.3.1";

Still getting the "no update path" error, until I found this old post, and then:

cp /usr/share/postgresql/9.5/extension/postgis--2.1.2--2.3.1.sql /usr/share/postgresql/9.3/extension/

Finally:

sudo pg_upgradecluster 9.3 main /data/db/postgresql/9.5/main # (took about 8 hours, b/c I think it has to dump and restore the whole DB?)

and we're all good. Ubuntu 16.04, Postgres server and client 9.5, PostGIS 2.3.

Can't believe that worked.

Moving a PostgreSQL table from an Ubuntu machine to a RHEL machine

pg_dump -Fc --file=(/.../pg_dump_file) --table=(tablename) --dbname=(database name)

(sftp the pg_dump_file to the new machine)
(install postgres by doing all of the below side bonus)

pg_restore --clean --create --dbname=(database name) --jobs=3 (pg_dump_file)

the -Fc means "binary format, not text"

Side bonus: installing PostgreSQL 9.4 on RHEL 6 (Red Hat Enterprise Linux,) (tips from here)

sudo yum localinstall https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-3.noarch.rpm # figure out what rpm you need here; if you just yum install postgresql you'll get an old version
sudo yum install postgresql94 postgresql94-server

sudo service postgresql-9.4 initdb # one-time initialization

sudo service postgresql-9.4 start # make the postgres server go

sudo service postgresql-9.4 on # make the postgres server go every time I log on

Note: on RHEL 7, you have to do these 3 lines instead of those last 3:

sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb

sudo systemctl start postgresql-9.4.service

systemctl enable postgresql-9.4.service # so it starts at boot

sudo yum install postgresql94-contrib # for hstore

sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm # to get the right EPEL rpm for RHEL 6; it's like adding a new APT repository in Ubuntu I think
sudo yum install postgis2_94 # more help here if you need

sudo su - postgres

psql

CREATE USER (myusername) WITH SUPERUSER;

CREATE DATABASE (dbname);
(go to that new database)
CREATE EXTENSION hstore;
CREATE EXTENSION postgis;
(quit postgres, e.g. with ctrl-d)
(log out of being the postgres user; e.g. with ctrl-d again)

Double note: I have 2 cloud machines, one on RHEL 6 and one on RHEL 7. The RHEL 7 one killed me at the "install postgis" step; adding the EPEL rpm didn't make all the build errors go away. (when I poked around for other EPEL rpms, I eventually found one that fixed most of the build errors, but then gave me another error where it was missing libpoppler.so-46. I think I was the first person ever to have this error. In a turn of lunacy, I spent part of the day building GDAL from source. Surprisingly, it kinda worked. But didn't get me to PostGIS.) So, if you googled this and are looking for an answer, I'm afraid I can't help. Sorry!

Useful PostgreSQL setup recipes

CREATE USER foo;
(or equivalently: CREATE ROLE foo WITH login;)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foo;
or
GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo;

I think these are all pretty self explanatory. Then the new user is up and running!
If you want the user to have a password:
CREATE USER foo WITH PASSWORD 'bar';

To get rid of the user: 
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM foo;

DROP ROLE foo;

ogr2ogr

Dear Future Dan,
I know you are trying to convert a shapefile to a GeoJSON file. I know this because you googled "site:ilessendata.blogspot.com ogr2ogr". Here is what you want to do:

ogr2ogr -t_srs EPSG:4326 -f GeoJSON output.json input.shp

Love, Past Dan

Dump a PostgreSQL table to json

This seems like it should be easy but it isn't, and I've had to look it up a couple times. Here's the magic juice:

copy (select array_to_json(array_agg(row_to_json(t)))
    from (
      select * from tweet_pgh limit 5
    ) t
) to '/home/dantasse/data_dump/foo.json';

gets 5 rows from the tweet_pgh table and dumps them to that file. (the directory has to be world-writeable I think, because it'll execute as the postgres user.)

Edit:
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT ST_AsGeoJSON(coordinates), * FROM tweet_austin) t) TO '/data/austin.json';
or just
COPY (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM tweet_austin) t) TO '/data/austin.json';
seems to work better when you're memory limited.