This post provides you with a PostgreSQL command cheat sheet to help you reference the most useful commands and syntax for developers on Mac OS. Whether you're a beginner or an experienced user, I hope this becomes a useful resource for increasing productivity and saving time for you working with PostgreSQL.
As a software developer who frequently works with databases, you would often find yourself using these commands daily. These commands allow you to efficiently manage your databases and perform various tasks such as creating tables, querying data, and managing user permissions.
Version
This works in PostgreSQL as of version 14.4 on 8 April 2023.
Connect to PostgreSQL
To connect to your local server
$ sudo psql -U <your-username> -d postgres
To connect to a local or remote database
$ psql -h localhost -p 5432 -U username -d databasename
You can replace localhost with the address of the remote server if you are connecting to a remote database and not your local database.
After entering this command, you will be prompted to enter the PostgreSQL password. Once authenticated, you can interact with the remote database as if it were a local database.
Creating database, user, and granting access
Creating a user, and granting users access to a database are fundamental tasks in database management that you'll always use in your development workflow. Here are the commands you need to know:
To create a new database:
create database databasename;
To create a new user:
create user username with password 'password';
To grant a user access to a database:
grant all privileges on database databasename to username;
Drop database
To drop a database:
drop database databasename;
Make sure to back up your database before dropping it or make sure you are not using it, as the process is irreversible
Change user password
To change your password:
alter user username with encrypted password 'new_password';
Dump and restore
Two essential tools that you'll find yourself using often are pg_dump and pg_restore. Both commands are invaluable for database administrators and developers who need to backup, migrate, or replicate databases
Dump
You'll find the pg_dump command to be a powerful tool for creating backups, migrating databases, and debugging. With pg_dump, you can easily create a text file containing SQL commands necessary to recreate your database's schema and data.
To use the pg_dump command, follow these steps:
$ pg_dump dbname > outfile
Replace dbname with the name of the database you want to dump, and outfile with the name of the file you want to save the dump to. For example, if you want to dump a database named awesome_app_database_production and save it to a file named awesome_app_database_production.sql, the command would be:
$ pg_dump awesome_app_database_production > awesome_app_database_production.sql
The pg_dump command will generate a SQL script that contains all the database objects and data.
Optionally, you can include additional options to customize the dump, such as the -U option to specify the username to connect to the database, or the -F option to specify the file format of the dump.
For example, to dump the database using the custom format and compress it with gzip, you can use the following command:
$ pg_dump -U myusername -F c awesome_app_database_production | gzip > awesome_app_database_production.dump.gz
This command will create a compressed dump file named awesome_app_database_production.dump.gz in the current directory.
Restore
To restore a database from a dump file, use the pg_restore command. To use pg_restore, use the following command:
$ pg_restore -d awesome_app_database_production awesome_app_database_production.sql
By default, pg_restore will recreate the database schema and data contained in the dump file.
