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