How To Install PostgreSQL on openSUSE
In this tutorial, we will show you how to install PostgreSQL on openSUSE. PostgreSQL, often known simply as Postgres, is a powerful, open-source object-relational database system with over 30 years of active development. It has earned a strong reputation for its robustness, scalability, and performance, making it a preferred choice for many enterprise-level applications and developers who demand a comprehensive SQL-compliant database system.
This article assumes you have at least basic knowledge of Linux, know how to use the shell, and most importantly, you host your site on your own VPS. The installation is quite simple and assumes you are running in the root account, if not you may need to add ‘sudo‘ to the commands to get root privileges. I will show you the step-by-step installation of the PostgreSQL database on openSUSE.
Prerequisites
- A server running one of the following operating systems: openSUSE.
- It’s recommended that you use a fresh OS install to prevent any potential issues.
- You will need access to the terminal to execute commands. openSUSE provides the Terminal application for this purpose. It can be found in your Applications menu.
- You’ll need an active internet connection to download PostgreSQL and its dependencies.
- You need access to a user account with sudo privileges. The sudo command allows you to run programs with the security privileges of another user (by default, the superuser). This is necessary for installing packages and making system-wide changes.
Install PostgreSQL on openSUSE
Step 1. First, it‘s always a good practice to update all packages on your system before installing any new software. This ensures that you have the latest security patches and bug fixes. Open your terminal and run the following command:
sudo zypper refresh sudo zypper update
Step 2. Installing PostgreSQL on openSUSE.
Next, we will install PostgreSQL along with some additional packages that provide useful utilities. Run the following command:
sudo zypper install postgresql-server postgresql-contrib
After installing PostgreSQL, the next step is to initialize the database cluster. A database cluster is a collection of databases that are managed by a single PostgreSQL server instance. Run the following command to initialize the database:
sudo postgresql-setup --initdb
Now, we need to enable the PostgreSQL service to start on boot and then start the service. Use the following commands:
sudo systemctl enable postgresql sudo systemctl start postgresql
Step 3. Secure PostgreSQL.
By default, PostgreSQL is configured to use ‘ident’ authentication, which means it associates PostgreSQL roles with a matching Linux system account. If a role exists within PostgreSQL, a Linux username with the same name will be able to sign in as that role.
For additional security, you might want to change the password for the PostgreSQL default user, which is ‘postgres‘. To do this, switch to the ‘postgres‘ user:
sudo su - postgres
Then open the PostgreSQL command prompt:
psql
Now, change the password:
\password postgres
Step 4. Create a New PostgreSQL Role and Database
Next, we will create a new PostgreSQL role and a new database. First, switch to the ‘postgres‘ user:
sudo su - postgres
Then, create a new role:
createuser --interactive
You will be prompted to provide a name for the new role and answer a few questions. Once the role is created, you can create a new database:
createdb mydatabase
Step 5. Configure PostgreSQL to Allow Remote Connections (Optional)
By default, PostgreSQL is configured to only accept connections from the same machine. If you want to allow remote connections, you will need to modify the PostgreSQL configuration file and the pg_hba.conf file. First, open the PostgreSQL configuration file:
sudo nano /var/lib/pgsql/data/postgresql.conf
Find the line that starts with #listen_addresses and change it to:
listen_addresses = '*'
Next, open the pg_hba.conf file:
sudo nano /var/lib/pgsql/data/pg_hba.conf
Add the following line to the end of the file:
host all all 0.0.0.0/0 md5
Finally, restart the PostgreSQL service to apply the changes:
sudo systemctl restart postgresql
Step 6. Basic PostgreSQL Commands.
Now that PostgreSQL is installed and configured, let’s go over some basic commands. To access the PostgreSQL prompt, use the following command:
psql
From here, you can run SQL commands to create tables, insert data, and query your databases.
Step 7. Backup and Restore.
It’s important to regularly backup your databases to prevent data loss. To backup a PostgreSQL database, use the pg_dump command:
pg_dump mydatabase > mydatabase.sql
To restore a PostgreSQL database, use the psql command:
psql mydatabase < mydatabase.sql
Congratulations! You have successfully installed PostgreSQL. Thanks for using this tutorial for installing the PostgreSQL database on your openSUSE system. For additional or useful information, we recommend you check the official PostgreSQL website.
