Skip to content

Instantly share code, notes, and snippets.

@hartleybrody
hartleybrody / psql-csv.md
Last active Dec 1, 2022
use CSV to bulk export/import data to postgres tables
View psql-csv.md

Intro

For when you need to insert or update millions of rows and don't want to mess around with scripting to batch individual SQL commands, it's worth using postgres's built in, super-fast support for importing and exporting data from the CSV file format.

Note: that we're using the \COPY command and not the COPY command, since it allows us to work with files locally available on our machine and not worry about transferring files to the database server's file system.

Via the psql manual on \COPY:

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

Export data from database t

@hartleybrody
hartleybrody / backup-analyze-redis.sh
Last active Aug 23, 2022
backup a production redis URL to your local redis server (and analyze key usage)
View backup-analyze-redis.sh
# 1. download a copy of prod db to localhost
# connect to remote redis database and
# download a snapshot of the entire database
# to a local file on your filesystem
# via https://stackoverflow.com/a/61365048/625840
# docs https://redis.io/docs/manual/cli/#remote-backups-of-rdb-files
redis-cli -u $PROD_REDIS_URL --rdb dump.rdb
View upgrading-postgresql-database.md

Postgres is usually run automatically on my laptop. I also have a weekly cronjob that does brew update and brew upgrade which sometimes bumps the version of portgres that I'm running. Newer versions of the postgres server software aren't necessarily compatible with the older version's data directory structure, and so you need to "upgrade" your database to see all of your old data in the upgraded postgres server.

Trying to start the database manually with

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Which gives an error message and tells me where to look (note that I passed in the log directory when I ran the command)

waiting for server to start.... stopped waiting

pg_ctl: could not start server

@hartleybrody
hartleybrody / import-db.sh
Last active Sep 21, 2022
Copy data from Heroku Postgres into local database
View import-db.sh
# copy/import data from heroku postgres to localhost pg database
# useful for copying admin's work on live site into local database to reproduce errors
# https://devcenter.heroku.com/articles/heroku-postgres-import-export
# take heroku pg snapshot and download
heroku pg:backups:capture
heroku pg:backups:download
# load the dump into local postgres database, assuming $DATABASE_URL set locally
View serialization.py
import json
import simplejson # pip install simplejson
import pickle
import cPickle
import marshal
import yaml # pip install pyyaml
import ujson # pip install ujson
from cStringIO import StringIO
import bson # pip install bson
@hartleybrody
hartleybrody / 0_reuse_code.js
Created Apr 2, 2014
Here are some things you can do with Gists in GistBox.
View 0_reuse_code.js
// Use Gists to store code you would like to remember later on
console.log(window); // log the "window" object to the console
@hartleybrody
hartleybrody / Bitwise Operators.md
Last active Aug 29, 2015
While learning about XORs during a basic crypto class, I decided to take a side track and learn about a term that was always shrouded in mystery -- bitwise operators. As always, I wrote these as my own notes for later reference but I'm publishing them here in case they help other people. Happy to accept pull requests for corrections.
View Bitwise Operators.md

(A bit of a tangent from crypto 101)

Bits and bytes

  • Core contepts:

    • Bitwise operators work on bits.
    • Bits are used to express data in binary form.
    • Bits can have a value of either 1 (ie TRUE) or 0 (ie FALSE).
  • Higher-level language constructs like floats and strings can all be represented as a series of bytes

  • 1 byte = 8 bits

@hartleybrody
hartleybrody / Javascript Variable Scoping.md
Last active Feb 3, 2019
Variable scoping in Javascript can be confusing so I set out to make notes of the basic rules so that I can reference them later. Hope these are useful to others, happy to talk pull requests for corrections.
View Javascript Variable Scoping.md

Javascript has two kinds of scope:

  1. Local (inside a function)
  2. Global (outside a function)

The var Keyword

  • Using the var keywords creates the variable in the current scope
    • If the current scope is global, then var is unnecessary (see below)
    • If the current scope is local, then you’re creating a local variable in the current scope
  • If you don’t use var, then Javascript goes up the “scope chain” to see if it’s already been declared
@hartleybrody
hartleybrody / new-tab.js
Last active Dec 15, 2015
Open all links on a page in a new window, assuming jQuery is mapped to the $ sign. Useful for when I want to leave a page open (ie, GitHub dashboard) and not have to constantly be reaching for my bookmarks.
View new-tab.js
$('a').each( function(index, item){
item.target = '_blank';
});