Skip to content

PostgreSQL

Jump to bottom
Eugene Odeluga edited this page Oct 26, 2022 · 16 revisions

Starting PostgreSQL server at Linux start up

Every time we start a terminal, the OS will execute .bashrc* file and we are going to modify the file to check if the postgres service is running or not. Why need to check? Since the terminal can be opened multiple times, the services don’t have to start multiple times. Instead only one single time. So, we need to have some mechanism to check the status of the services.

Note: .bashrc is located in your home directory which is usually at /home/<username>/.bashrc

# Start postgres server at start up
if ! pgrep -x "postgres" >/dev/null; then
    sudo pg_ctlcluster 12 main start
    echo "postgres service started using: sudo pg_ctlcluster 12 main start"
fi

Stop elevation request prompts

If you immediately start your terminal after setting up your .bashrc file, you will notice that you will be asked for a password since starting service requires you to be a superuser. Hence, we are going to fix this issue by whitelisting the command to not have to input a password.

We are going to create a file in sudoers.d since we don’t want to modify it with the operating system file. This will prevent any issue if the operating system needs to update the actual sudoers file in the event of upgrades. First, we need to create a file where will put the filename as skip_sudo_pg.

sudo visudo -f /etc/sudoers.d/skip_sudo_pg

Then, the content of the file

%sudo   ALL=(ALL) NOPASSWD:/usr/bin/pg_ctlcluster

Note: The following restore instructions assume the use of default postgres user

Creating a database

  • Run psql as the postgres user

sudo -u postgres psql

  • Create a database

CREATE DATABASE <name>;

  • Press Ctrl + Z to exit

Restoring a database

If the database does not exist, then create a database with the same name as the one to restore.

Restoring from a plain .sql dump file

sudo -u postgres psql <db_name> < <path to .sql file>

Restoring from a custom dump file

  • Similar to the above process except the restore tool is pgrestore

sudo -u postgres pg_restore -d <db_name> <path to .dump file>

Change the password of a user in the database

  • Start psql

sudo -u postgres psql

  • Enter the SQL

ALTER USER <username> WITH PASSWORD '<password>';

  • Ctrl + Z to exit

Clone this wiki locally