PostgreSQL, often simply referred to as Postgres, is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. It’s a favorite among developers for complex data operations and high-volume applications. This Tutorial will guide you through the process of installing and setting up PostgreSQL on your Ubuntu 24.04 server.
Before you begin, ensure you have a fresh installation of Ubuntu 24.04 and a user with sudo privileges. It’s always a good practice to update your system’s package list to ensure you’re working with the latest available software.
Step 1: Update System Packages
Open your terminal and run the following commands to update your package list:
sudo apt update
sudo apt upgrade -y
Step 2: Install PostgreSQL
Ubuntu’s default repositories include PostgreSQL. You can install the server and client packages using `apt`:
sudo apt install postgresql postgresql-contrib -y
The `postgresql-contrib` package provides additional utilities and functionalities for PostgreSQL, which can be useful for various tasks.
Step 3: Verify Installation and Service Status
After installation, the PostgreSQL service should start automatically. You can verify its status using `systemctl`:
sudo systemctl status postgresql
You should see output indicating that the service is `active (exited)` or `active (running)`. If it’s not running, you can start it with:
sudo systemctl start postgresql
To enable PostgreSQL to start on boot:
sudo systemctl enable postgresql
Step 4: Accessing the PostgreSQL Prompt
By default, PostgreSQL creates a user named `postgres` that corresponds to the default `postgres` database. To access the PostgreSQL prompt, you need to switch to the `postgres` system user:
sudo -i -u postgres
Once you are the `postgres` user, you can access the PostgreSQL prompt using the `psql` command:
psql
You are now inside the PostgreSQL prompt. To exit, type `q` and press Enter.
q
To return to your regular sudo user, type `exit`.
Step 5: Create a New PostgreSQL User and Database
It’s generally not recommended to use the default `postgres` user for your applications. Let’s create a new user and a dedicated database for it. Switch back to the `postgres` user:
sudo -i -u postgres
Now, create a new PostgreSQL user. Replace `your_username` with your desired username:
createuser --interactive
Follow the prompts to set the username and whether it should be a superuser (usually ‘n’ for applications). Alternatively, you can create a user directly:
createuser --pwprompt your_username
You’ll be prompted to enter and confirm a password for the new user.
Next, create a database for this user. It’s common practice to create a database with the same name as the user:
createdb your_database_name
To grant ownership of the database to your new user:
psql -c "ALTER DATABASE your_database_name OWNER TO your_username;"
Exit the `postgres` user session:
exit
Step 6: Connect to the New Database
You can now connect to your newly created database using your new user. Replace `your_database_name` and `your_username` with your chosen names:
psql -d your_database_name -U your_username
You’ll be prompted for the password you set for `your_username`. Once connected, you can perform database operations. Type `q` to exit.
Step 7: Configure Remote Access (Optional)
By default, PostgreSQL only listens for connections from `localhost` (127.0.0.1). If you need to connect to your PostgreSQL server from a remote machine, you’ll need to modify two configuration files:
Edit `postgresql.conf`
Locate the `postgresql.conf` file, typically in `/etc/postgresql/16/main/` (the `16` refers to the PostgreSQL version, adjust if different). Open it with a text editor:
sudo nano /etc/postgresql/16/main/postgresql.conf
Find the line `#listen_addresses = ‘localhost’` and change it to:
listen_addresses = '*'
This tells PostgreSQL to listen on all available network interfaces. Save and exit the file (Ctrl+O, Enter, Ctrl+X).
Edit `pg_hba.conf`
Next, you need to configure client authentication. Open `pg_hba.conf`:
sudo nano /etc/postgresql/16/main/pg_hba.conf
Add a line at the end of the file to allow connections from specific IP addresses or an entire network. For example, to allow connections from any IP address using MD5 password authentication:
host all all 0.0.0.0/0 md5
Or, to allow connections from a specific IP range (e.g., 192.168.1.0/24):
host all all 192.168.1.0/24 md5
Save and exit the file.
Restart PostgreSQL Service
After making changes to the configuration files, you must restart the PostgreSQL service for them to take effect:
sudo systemctl restart postgresql
Conclusion
You have successfully installed PostgreSQL on your Ubuntu 24.04 server, created a new user and database, and optionally configured it for remote access. PostgreSQL is now ready for you to build powerful applications and manage your data efficiently. Explore the extensive PostgreSQL documentation for advanced features and optimization techniques.
