Intro to Postgres for a MySQL User

When I started building web apps, I used PHP and MySQL, and I stuck with MySQL until coming to the Elixir ecosystem, where Postgres is much more prominent. I gave a new database a try, and I like the experience I’ve had, but I do get tripped up from time to time by the differences in the utility commands.

Login

The psql command feels very similar to mysql. With one argument, it will connect to that database using the current linux user.

psql <database>

A number of flags are similar, but changing username requires a capital -U.

psql -h <host> -U <user> <database>

Each of those can also be specified in an environment variable.

export PGHOST=host PGUSER=user PGDATABASE=database
psql

Navigate

Navigation is the most dissimilar. I’ve typed “show tables” into psql more times than I care to admit.

To list databases, use \l. Switch databases with \c <database>.

postgres-# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 phoenix   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
postgres-# \c phoenix
You are now connected to database "phoenix" as user "postgres".

Once you’ve selected a database, you can list tables with \dt. To see the columns in a table, use \d <table>. You can view extended information by adding + after each command. For example, \dt+.

phoenix-# \dt
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | schema_migrations | table | postgres
 public | users             | table | postgres
 public | users_tokens      | table | postgres
(3 rows)
phoenix-# \dt users*
 Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
 public | users        | table | postgres
 public | users_tokens | table | postgres
(2 rows)
phoenix-# \d users
                               Table "public.users"
     Column      |              Type              | Collation | Nullable | Default
-----------------+--------------------------------+-----------+----------+---------
 id              | uuid                           |           | not null |
 email           | citext                         |           | not null |
 hashed_password | character varying(255)         |           | not null |
 confirmed_at    | timestamp(0) without time zone |           |          |
 inserted_at     | timestamp(0) without time zone |           | not null |
 updated_at      | timestamp(0) without time zone |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_index" UNIQUE, btree (email)
Referenced by:
    TABLE "devices" CONSTRAINT "devices_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "goals" CONSTRAINT "goals_user_fkey" FOREIGN KEY ("user") REFERENCES users(id)
    TABLE "users_tokens" CONSTRAINT "users_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
phoenix-# \dt+
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+-------------------+-------+----------+-------------+---------------+------------+-------------
 public | users             | table | postgres | permanent   | heap          | 16 kB      |
 public | users_tokens      | table | postgres | permanent   | heap          | 16 kB      |
 public | voice_requests    | table | postgres | permanent   | heap          | 16 kB      |
(3 rows)

Create Databases

You can run any SQL command from the command-line with psql and the -c flag.

psql -c "CREATE DATABASE <database> WITH OWNER <user>;"

Some distributions may come with the createdb utility:

Setup Users

Creating users is a bit easier with Postgres because there’s no host limitations.

postgres-# CREATE USER <user> WITH PASSWORD '<passsord>';

Inside the psql console, you can change a user’s password with \password.

postgres-# \password <user>

Dump and Restore

Postgres uses the pg_dump to export databases and it works just like mysqldump.

pg_dump <database> > database.sql

You can limit the export to certain tables:

pg_dump -t users* <database> > database-users.sql

pg_dump takes all of the common arguments (host, user, database) from command line or as environment variables.

Backup Consistency

pg_dump automatically makes a consistent backup. It behaves similarly to mysqldump if you had used the —single-transaction flag.

Restoring a .sql file is different. Do not use redirection to send SQL commands to psql. Instead, psql provides the -f flag.

psql <database> -f database.sql