PostgreSQL Commands For Software Developers Using Mac OS

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.

Wei-Ming Thor

I create practical guides on Software Engineering, Data Science, and Machine Learning.

Background

Full-stack engineer who builds web and mobile apps. Now, exploring Machine Learning and Data Engineering. Read more

Writing unmaintainable code since 2010.

Skill/languages

Best: JavaScript, Python
Others: Android, iOS, C, React Native, Ruby, PHP

Work

Engineering Manager

Location

Kuala Lumpur, Malaysia

Open Source
Support

Turn coffee into coding guides. Buy me coffee