Walk through the key steps you would follow when installing a new PostgreSQL or MySQL instance on a Linux server.

Question

Accepted Answer

Installing a database on a Linux server is a foundational task for a junior DBA. While the specific commands vary between database systems and Linux distributions, the overall process follows a consistent pattern that ensures a secure and properly configured installation.

The first step is to prepare the server. Verify that the server meets the minimum hardware requirements for the expected workload. Check available disk space, memory, and CPU resources. Ensure the operating system is up to date with security patches. For production servers, use dedicated disks or partitions for the database data directory to isolate database I/O from the operating system.

Next, add the official repository for your database. Using the official repository rather than the default distribution packages ensures you get the latest stable version with all patches. For PostgreSQL, this means adding the PostgreSQL Apt repository on Debian/Ubuntu or the PostgreSQL Yum repository on Red Hat/CentOS. For MySQL, you would add the MySQL APT or YUM repository from the official MySQL site.

Install the database packages using the package manager. For PostgreSQL on Ubuntu, this is typically apt install postgresql postgresql-contrib. For MySQL, it is apt install mysql-server. The installation process usually creates a system user for the database service, initializes a default data directory, and starts the service.

After installation, secure the initial configuration. For PostgreSQL, the default setup creates a superuser named postgres with local peer authentication. You should set a strong password for this user and configure pg_hba.conf to control which hosts can connect and which authentication methods they use. For MySQL, run the mysql_secure_installation script which prompts you to set a root password, remove anonymous users, disable remote root login, and remove the test database.

Configure the essential parameters for your workload. For PostgreSQL, the key parameters in postgresql.conf include shared_buffers, which should typically be set to about 25 percent of available RAM, work_mem for sort operations, effective_cache_size to inform the query planner about available memory, and max_connections based on your expected concurrent connections. For MySQL, key parameters in my.cnf include innodb_buffer_pool_size, which should be about 70 to 80 percent of available RAM for InnoDB-heavy workloads, max_connections, and innodb_log_file_size.

Set up the firewall to allow connections only from authorized networks. Use iptables or firewalld to restrict access to the database port, which is 5432 for PostgreSQL and 3306 for MySQL. Never expose the database port to the public internet without additional security layers.

Finally, verify the installation by connecting to the database, running a test query, checking the service status, and reviewing the log files for any warnings or errors. Set up log rotation to prevent log files from consuming all available disk space.