I'm transitioning my databases from MySQL to PostgreSQL. Overall it's been an easy transition. One of the main differences that I had to adjust to is that by default postgres uses the unix user accounts to handle authentication into the CLI. This can be changed in the settings, and it also means that you might want to set the password for the postgres unix user.

Also it's good to note that postgres automatically translates non-strings to lowercase. This is an issue if you do things like camel casing your tables and columns, so it's best to avoid doing so if you're into this habit.

I do find the command lines to be more streamlined and the config to be more straight forward, but that's really just a personal preference.

Here's a rundown of the commands I often use. I'm posting them here for my convenience, but perhaps someone else might also find these useful.

# login
psql -U <user>
psql -U <user> -d <database>
psql -U <user> -h <host> -d <database>


# user management
CREATE USER <user> WITH PASSWORD '<password>'
ALTER USER <user> WITH PASSWORD '<password>'
DROP USER <user>

createuser -D -A -P <user>
psql -U <admin> -c "CREATE USER <user> WITH PASSWORD '<password>'" -d template1


# database
CREATE DATABASE <database>
CREATE DATABASE <database> OWNER <user>
DROP DATABASE <database>

GRANT ALL PRIVILEGES ON DATABASE <database> TO <user>
ALTER TABLE <table> OWNER TO <user>

createdb -O <user> <database>


# install
apt-get install posgresql
apt-get install python-psycopg2

or

apt-get install libpq-dev
pip install psycopg2


# configuration
# pg_hba.conf
host all all 0.0.0.0/0 md5 # open up all connections, used for dev

# postgresql.conf
listen_addresses = '*' # listen for addresses


# dump and load data
pg_dump <database> > <file.sql>
psql <database> < <file.sql>

# from http://developer.postgresql.org/pgdocs/postgres/app-pgdump.html
To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump

To dump a single table named mytab:

$ pg_dump -t mytab mydb > db.sql

To dump all tables whose names start with emp in the detroit schema,
except for the table named employee_log:

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

To dump all schemas whose names start with east or west and end in gsm,
excluding any schemas whose names contain the word test:

$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

To dump all database objects except for tables whose names begin with ts_:

$ pg_dump -T 'ts_*' mydb > db.sql

To specify an upper-case or mixed-case name in -t and related switches,
you need to double-quote the name; else it will be folded to lower case (see Patterns).
But double quotes are special to the shell, so in turn they must be quoted.
Thus, to dump a single table with a mixed-case name, you need something like

$ pg_dump -t '"MixedCaseName"' mydb > mytab.sql


# export csv
psql -U <user> <database>
\f ','
\a
\t
\o file.csv
SELECT <statement>
\o
\q

Here's some of the common social networks and their associated share link formats. They are all similar, but of course there's no standard . . . that would be too easy.

These are subject to change at any time. Please let me know if I've missed something.

<!-- twitter -->
<a href="http://twitter.com/home?status={{ title }}
{{ request.build_absolute_uri }}" title="Click to share this post on Twitter">twitter</a>
<!-- twitter -->

<!-- facebook -->
<a href="http://www.facebook.com/share.php?u={{ request.build_absolute_uri }}&t=<title of content>">facebook</a>
<!-- facebook -->

<!-- reddit -->
<a href="http://www.reddit.com/submit?url={{ request.build_absolute_uri }}&title={{ title }}">reddit</a>
<!-- reddit -->

<!-- digg -->
<a href="http://digg.com/submit?url={{ request.build_absolute_uri }}&title={{ title }}&bodytext={{ description }}">digg</a>
<!-- digg -->

<!-- delicious -->
<a href="http://delicious.com/save?url={{ request.build_absolute_uri }}&title={{ title }}">delicious</a>
<!-- delicious -->

<!-- myspace -->
<a href="http://www.myspace.com/index.cfm?fuseaction=postto&u={{ request.build_absolute_uri }}&t={{ title }}
&c={{ contetent }}">myspace</a>
<!-- myspace -->

<!-- stumbleupon -->
<a href="http://www.stumbleupon.com/submit?url={{ request.build_absolute_uri }}">stumbleupon</a>
<!-- stumbleupon -->

<!-- linkedin -->
<a href="http://www.linkedin.com/shareArticle?mini=true&url={{ request.build_absolute_uri }}&title={{ title }}
&summary={{ description }}&source={{ source }}">linkedin</a>
<!-- linkedin -->

<!-- google bookmarks -->
<a href="http://www.google.com/bookmarks/mark?op=add&bkmk={{ request.build_absolute_uri }}&title={{ title }}">google bookmarks</a>
<!-- google bookmarks -->

Virtualenv and pip install typically go together so I'm combining their cheatsheets. These are the commands I use the most. This is pasted here for quick reference.

# create new virtual environment
virtualenv <name>
virtualenv <name> --no-site-packages

# activate and deactivate virtualenv 
source <name>/bin/activate
deactivate

# pip install
pip install PACKAGE
pip install PACKAGE==VERSON
pip install PACKAGE>=VERSION
pip install PACKAGE --upgrade
pip install -r /path/to/requirements.txt

# pip uninstall
pip uninstall PACKAGE

# pip freeze
pip freeze > requirements.txt

# pip install tar bundle
pip install http://example.com/path/to/tar/package.tgz

# git
pip install -e git://github.com/user/MyProject.git#egg=MyProject
pip install -e git+http://git.myproject.org/MyProject/#egg=MyProject
pip install -e git+ssh://git@myproject.org/MyProject/#egg=MyProject

pip install -e git://git.myproject.org/MyProject.git@master#egg=MyProject
pip install -e git://git.myproject.org/MyProject.git@v1.0#egg=MyProject
pip install -e git://git.myproject.org/MyProject.git@da39a3ee5e6b4b0d3255bfef95601890afd80709#egg=MyProject

# mercurial
pip install -e hg+http://hg.myproject.org/MyProject/#egg=MyProject
pip install -e hg+https://hg.myproject.org/MyProject/#egg=MyProject
pip install -e hg+ssh://hg@myproject.org/MyProject/#egg=MyProject

pip install -e hg+http://hg.myproject.org/MyProject/@da39a3ee5e6b#egg=MyProject
pip install -e hg+http://hg.myproject.org/MyProject/@2019#egg=MyProject
pip install -e hg+http://hg.myproject.org/MyProject/@v1.0#egg=MyProject
pip install -e hg+http://hg.myproject.org/MyProject/@special_feature#egg=MyProject

# subversion
pip install -e svn+http://svn.myproject.org/svn/MyProject/trunk@2019#egg=MyProject

# bazaar
pip install -e bzr+http://bzr.myproject.org/MyProject/trunk/#egg=MyProject
pip install -e bzr+sftp://user@myproject.org/MyProject/trunk/#egg=MyProject
pip install -e bzr+ssh://user@myproject.org/MyProject/trunk/#egg=MyProject
pip install -e bzr+ftp://user@myproject.org/MyProject/trunk/#egg=MyProject

pip install -e bzr+https://bzr.myproject.org/MyProject/trunk/@2019#egg=MyProject
pip install -e bzr+http://bzr.myproject.org/MyProject/trunk/@v1.0#egg=MyProject

Here's some of the MySQL commands that I regularly use. I'm posting this so I have a place to quickly look these up, but perhaps someone else might find these useful as well.

# login
mysql -u root -p

USE database_name;
SHOW tables;
DESCRIBE table_name 

# create database
CREATE DATABASE django_project;
SHOW DATABASES;

# create user
CREATE USER username IDENTIFIED BY 'pass1';

# grant access
GRANT ALL ON django_project.* TO username;

# Export A MySQL Database
mysqldump -u username -p database_name > FILE.sql
mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL
mysqldump --user=XXXXXXXX --password=XXXXXXX DB_NAME1 DB_NAME2 > /PATH/TO/DUMPFILE.SQL
mysqldump --user=XXXXXXXX --password=XXXXXXXX DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

# Import A MySQL Database
mysql -u username -p database_name < FILE.sql
mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

# duplicate table
CREATE TABLE newname SELECT * FROM sourcename

# alter table
ALTER TABLE profiles_category ASS header varchar(100) NOT NULL AFTER icon;
ALTER TABLE profiles_category MODIFY icon varchar(100) NOT NULL; modify attributes
ALTER TABLE profiles_category CHANGE icon image varchar(100) NOT NULL;    rename
ALTER TABLE profiles_category DROP icon;

# update table
UPDATE profiles_category SET header='name' WHERE id=1;