Installing and setting up Postgres in Ubuntu
At work, we handle a lot of short-term projects, typically lasting 3-8 weeks. These projects don't require a complex deployment setup. Instead, we deploy each project on a single VPS instance, with all the stacks bundled together on the same system. Sure, it's a single point of failure and not the ideal setup, it however matches our clients’ budget and allows us to deliver quickly. Luckily, we've…
Installing and setting up PostgreSQL in Ubuntu involves a few key steps. First, the PostgreSQL package along with its contrib modules can be installed using the command `sudo apt install postgres postgresql-contrib`. After installation, PostgreSQL runs as a service managed by systemd. To initiate the service, the command `sudo systemctl start postgres.service` is used. To ensure it starts automatically after a reboot, `sudo systemctl enable postgres.service` is executed.
Next, to establish access to the database, a new user must be created. Initially, a default `postgres` user is generated with administrative privileges. To create a new user, one needs to switch to the `postgres` user with `sudo su postgres`. Following this, the `createuser` command can be run interactively to set the username and decide if the role will have superuser privileges.
Creating a database is straightforward; it can be done using the `createdb` command followed by the desired database name. However, the new user needs permissions to interact with the database. This is achieved by logging into PostgreSQL with `psql` and executing SQL queries to grant specific rights, such as `GRANT CONNECT ON DATABASE {{database_name}} TO {{username}};`, `GRANT pg_read_all_data TO {{username}};`, and `GRANT pg_write_all_data TO {{username}};`.
Finally, to authenticate with the created user, their password must be set. As the `postgres` user, who has the highest authority, this is accomplished using the command `ALTER USER {{username}} WITH PASSWORD {{new_password}};`. After setting the password, exiting both the SQL shell (`\q`) and the `postgres` user shell is required.
While alternative methods like Docker images or Ansible scripts can simplify setup, especially for large-scale deployments, this manual approach allows for tailored configurations suited to specific needs.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.