Part IV — PostgreSQL and MySQL in Practice

Chapter 14. Installing and Configuring PostgreSQL and MySQL

Everything so far has assumed a running database. This chapter builds one — twice. Installing PostgreSQL and MySQL, connecting to them, creating users, and troubleshooting connections is the moment the book's examples become your environment, and it is deliberately placed before the platform chapters so that Chapters 15–17 can be run, not just read.

Installation is also where the two platforms' personalities first show: PostgreSQL arrives as a tight, complete server with one command-line client; MySQL arrives with an installer, a configuration wizard, and a hardening script. Both are easy on Windows and Linux when you know the handful of concepts this chapter teaches: the server process, the data directory, the service, the authentication file, and the client.

After studying this chapter you will be able to:

• Describe each platform's installable components and what each is for.
• Install PostgreSQL and MySQL Server on Windows and Linux.
• Install and choose among clients and graphical tools.
• Work fluently in psql and the mysql client.
• Tour pgAdmin and MySQL Workbench.
• Create databases and users on both platforms and grant access.
• Connect from the command line and from applications.
• Configure the basics and troubleshoot the classic connection failures.

14.1 PostgreSQL architecture and components

Chapter 4 drew the picture; installation names the parts you will actually touch:

• Server (postgres on Linux, a Windows service named postgresql-x64-16): the postmaster process plus background workers, owning one data directory (PGDATA — /var/lib/postgresql/16/main on Debian-family Linux, chosen by the installer on Windows).
• Client tools: psql (the CLI), pg_dump/pg_restore (backups, Chapter 21), pg_config, createdb/dropdb.
• Three configuration files in the data directory, the ones every DBA learns first: postgresql.conf (server settings — port, memory, logging), pg_hba.conf (host-based authentication: who may connect, from where, how they prove it), and pg_ident.conf (optional OS-user mapping).
• Roles: PostgreSQL unifies users and groups as roles; a role with LOGIN can connect. Fresh installs create one superuser role named postgres — the account you use first and then, per Chapter 20's discipline, stop using.

One default worth memorizing: PostgreSQL listens on 5432, and — fresh install — only on local connections. Making it network-reachable is a deliberate change (Section 14.11), which is why local labs work out of the box and remote access fails until configured.

14.2 MySQL architecture and components

MySQL's parts, same lens:

• Server (mysqld), a Windows service or a Unix daemon, owning a data directory (/var/lib/mysql, or the installer's path on Windows) in which each database is a subdirectory of files.
• Client tools: mysql (the CLI), mysqldump and mysqlpump (backups), mysqladmin (server administration), mysqlshow.
• Configuration file: my.cnf (Linux, commonly /etc/mysql/my.cnf) or my.ini (Windows), holding settings under sections like [mysqld] — port, bind-address, InnoDB buffer pool size, logging.
• Users: MySQL accounts are user@host pairs — 'app'@'localhost' and 'app'@'10.0.0.5' are different accounts with different passwords. Fresh installs create root — superuser, and on Linux often authenticated by a socket/auth plugin.

The personality difference previewed in Chapter 4 reappears at install time: MySQL's storage engines mean the server and the physical layer are separately chosen (InnoDB by default for everything that matters), and its installer culture ( wizards, bundles with Workbench) reflects the product's desktop-friendly history.

14.3 Installing PostgreSQL on Windows and Linux

Windows. Download the EDB installer for PostgreSQL 16 from postgresql.org; the wizard walks through components (server, pgAdmin 4, Stack Builder — skip Stack Builder initially), the data directory, and — the step that matters — the postgres superuser password (record it; you will need it for every local connection), the port (5432), and the locale. Finish, and the server runs as a Windows service. Verify from PowerShell:

PS> psql -U postgres -h localhost
Password for user postgres: ********
postgres=# SELECT version();

Linux (Debian/Ubuntu family). The distribution repository is usually one major version behind; the PostgreSQL Apt Repository is the supported route:

sudo apt install postgresql postgresql-contrib
sudo systemctl status postgresql       # active (running)

Red Hat-family systems use dnf install postgresql-server postgresql-contrib followed by postgresql-setup --initdb. Linux installs authenticate the postgres superuser locally without a password via peer authentication — connect as the OS user:

sudo -u postgres psql

Both routes land in the same place: a running server on 5432, a postgres superuser, and an empty cluster awaiting CREATE DATABASE university;.

14.4 Installing MySQL Server on Windows and Linux

Windows. The MySQL Installer (dev.mysql.com) offers setup types — Server only for this book — then the configuration wizard: standalone server, port 3306, root password (record it), and optionally a Windows service. MySQL 8's default authentication plugin (caching_sha2_password) matters to application drivers (Section 14.10). Verify:

PS> mysql -u root -p
Enter password: ********
mysql> SELECT VERSION(), @@version_comment;

Linux (Debian/Ubuntu).

sudo apt install mysql-server
sudo mysql_secure_installation     # root password, remove anonymous users, ...
sudo systemctl status mysql        # active (running)

mysql_secure_installation is the post-install ritual: set the root password, remove anonymous accounts, disallow remote root, drop the test database. On Linux, root initially authenticates via the auth_socket plugin — sudo mysql gets you in; creating a password-using account (Section 14.9) is the first configuration act. Red Hat-family: dnf install mysql-server then systemctl start mysqld.

14.5 Installing database clients and graphical tools

The command-line clients install with the server, and both are also available standalone (PostgreSQL's psql alone via postgresql-client; MySQL's via mysql-client) — the right choice for application servers that must reach the database without running one.

The GUI layer: pgAdmin 4 ships with the Windows PostgreSQL installer (web-interface architecture: a local server and a browser tab) and is installable separately on any platform. MySQL Workbench comes via the MySQL Installer or standalone, and is the standard MySQL GUI on all platforms. DBeaver is the strong vendor-neutral alternative — one client, any engine, including both of ours — and the right choice for the Chapter 17 side-by-side work. One practice rule for all GUIs: learn the CLI first, keep the GUI for schema diagrams, data browsing, and plan reading. The GUI generates the same SQL you would type (its "SQL preview" panes are excellent learning aids); the CLI is what a 3 a.m. incident gives you.

14.6 PostgreSQL command-line client: psql

psql is the most admired database CLI in the industry, and its meta-commands — backslash escapes — are the reason:

A session against the university database:

$ psql -U student -h localhost -d university
university=> \dt
university=> SELECT COUNT(*) FROM enrollment;
university=> \q

The prompt encodes state (university=> in a transaction? it becomes university*> after an error, university=# for superuser) — glance at it before wondering why a statement behaved oddly. Multi-line statements continue until the semicolon; a statement you want to abandon mid-typing ends with \c... in psql that key is Ctrl-C or a strategically placed ;. Scripts run non-interactively with psql -f script.sql, the form every later chapter's laboratories use.

14.7 MySQL command-line client

The mysql client speaks SQL plus its own escapes — and its meta-commands are statements, not backslashes (a heritage worth knowing when a shell eats your backslashes):

$ mysql -u student -p -h localhost university
Enter password: ********
mysql> SHOW TABLES;
mysql> SELECT COUNT(*) FROM enrollment;
mysql> quit

Prompt states here are prefixes: mysql>, continuing ->, and after an error the warning; unterminated statements cancel with \c. --table and --batch flags control output formatting for scripts, the MySQL counterparts of psql's aligned and unaligned modes.

14.8 pgAdmin and MySQL Workbench

Both GUIs cover the same five jobs, with different accents.

pgAdmin 4: browser-based; a browser tree of servers → databases → schemas → tables; a query tool with EXPLAIN visualization (Chapter 19's plans render as diagrams); data editing grids with filter/sort; user and privilege dialogs; and backup/restore dialogs that wrap pg_dump/pg_restore — with a "SQL" tab always showing the command generated, which is how a GUI teaches you the CLI.

MySQL Workbench: a desktop application; its distinctive feature is the EER diagram editor — reverse-engineer the university schema into a diagram, edit it, and forward-engineer DDL back out (Chapter 5's ERDs, as software); plus a SQL editor with visual EXPLAIN, a schema inspector, user administration, and a migration wizard (Chapter 17's portability work, as software).

Both connect with exactly the parameters of Section 14.10 — host, port, user, password, database — which is the point of knowing them by hand: every tool, driver, and ORM in this book asks for those five facts and nothing else.

14.9 Creating databases and users

The administrator's first two acts, on both platforms:

-- PostgreSQL: roles, and a database owned by a working role
CREATE ROLE registrar WITH LOGIN PASSWORD 'change-me';
CREATE DATABASE university OWNER registrar;

-- grants (Chapter 20 expands)
GRANT CONNECT ON DATABASE university TO registrar;

-- MySQL: user@host accounts, and database-scoped privileges
CREATE USER 'registrar'@'localhost' IDENTIFIED BY 'change-me';
CREATE DATABASE university;
GRANT ALL PRIVILEGES ON university.* TO 'registrar'@'localhost';
FLUSH PRIVILEGES;      -- older MySQL practice; 8.0 grants apply immediately

The structural difference matters for everything later: PostgreSQL privileges attach to objects in databases (and roles can inherit from other roles — group modeling); MySQL privileges attach to database/table patterns per user@host (university.* is a pattern, and % is the wildcard host). PostgreSQL CREATE ROLE/CREATE USER are synonyms (users are roles with LOGIN); MySQL has only users. And the superuser discipline starts here: create registrar, work as registrar, and keep postgres/root for administration only — Chapter 20's least privilege, practiced from day one.

14.10 Connecting to a database server

Every connection — CLI, GUI, driver, ORM — supplies the same five facts: host, port, database, user, password. Command-line spellings:

psql -h db.example.edu -p 5432 -U registrar -d university
mysql -h db.example.edu -P 3306 -u registrar -p university

(-p with no attached password makes MySQL prompt — never type passwords into the command line where shell history keeps them.) Environment variables carry defaults: PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD on the PostgreSQL side; MySQL reads [client] sections of my.cnf — the credential-file pattern that beats env-vars for shared machines. PostgreSQL also offers connection URIs: psql "postgresql://registrar@db.example.edu:5432/university" — the form every modern driver accepts, and the one your application's configuration will hold (Chapter 23).

Drivers speak these same five facts: JDBC URLs (jdbc:postgresql://host:5432/university), Python DSNs and URLs, PHP DSNs. When a driver fails where the CLI succeeds, the difference is almost always one of: the driver isn't installed in the application's environment, MySQL 8's caching_sha2_password versus an older client library (fix: update the driver or create the account with mysql_native_password), or SSL requirements (sslmode, Chapter 20).

14.11 Basic configuration and troubleshooting

The four settings every installation eventually touches — always followed by a restart/reload:

Making a server network-reachable is a pair of changes: the listen address above, and the authentication layer — PostgreSQL's pg_hba.conf needs a host university registrar 10.0.0.0/24 scram-sha-256 line (then pg_ctl reload); MySQL's user must exist as 'registrar'@'10.0.0.%' (host patterns again) and the OS/firewall must open the port on both platforms.

The troubleshooting table — the five classic failures and their meanings:

Methodical habit: work the ladder bottom-up — network (ping the host, telnet host 5432), server (is the service running), authentication (right user, right method, right host pattern), authorization (right grants), database (right name). Ninety percent of "the database is down" tickets die on rung two.

Chapter Summary

• PostgreSQL installs as server + psql + config trio (postgresql.conf, pg_hba.conf, pg_ident.conf) with a postgres superuser; MySQL installs as mysqld + mysql client + my.cnf with root and user@host accounts.
• Windows uses the EDB and MySQL installers (record the superuser passwords); Linux uses apt/dnf plus mysql_secure_installation; Linux PostgreSQL connects first via sudo -u postgres psql.
• Clients: psql and mysql are the professional baseline (meta-commands: backslash escapes vs SHOW/SOURCE statements); pgAdmin and Workbench add GUI strengths — the latter's EER diagramming; DBeaver spans engines.
• Users and databases: PostgreSQL roles with object grants and database ownership; MySQL user@host accounts with pattern privileges (university.*).
• Every connection supplies host, port, database, user, password — CLI flags, environment variables, connection URIs, JDBC/Python URLs.
• The four basic settings (listen address, port, memory, slow logging) plus the paired authentication change make a server network-reachable.
• Troubleshooting climbs a ladder: network → service → authentication → authorization → database; the five classic errors each name their rung.

Key Terms

Laboratory Exercises

1. Install both servers (any OS route), record versions, and capture service status: SELECT version(); and systemctl status (or the Services panel) for each. Expected results: PostgreSQL 16+ and MySQL 8.0+; both services active.
2. Load the university schema and data (Appendix H) into both servers using \i and SOURCE, then run the six-count verification of Section 9.10 on each. Expected result: 5, 6, 12, 10, 13, 28 on both platforms.
3. Client fluency drill: list tables, describe enrollment, list users/roles, and show session status — once with meta-commands, once with information_schema queries — on both clients. Expected result: the same six tables and role/user lists from both routes.
4. Create the registrar account and university_dev database on both platforms exactly as Section 14.9, connect as registrar, and prove the superuser discipline by failing to create a database with that account. Expected result: connection succeeds; CREATE DATABASE is refused (no CREATEDB privilege / no global grant).
5. Connect five ways to your PostgreSQL server: flags, env-vars, connection URI, pgAdmin, and a ~/.pgpass file; then break one deliberately (wrong port) and identify the error's rung on the connection ladder. Expected result: four working routes; wrong port yields "connection refused" — rung one/two.
6. Configuration round-trip in a dev environment: change each server's port (5433/3307), restart, connect on the new port, change back. Then enable slow-query logging on each and record where the log file lands. Expected result: connections fail on old port, succeed on new; slow logs appear at the configured paths; settings restored.

Review Questions and Exercises

1. Name PostgreSQL's three configuration files and what each governs. postgresql.conf — server settings; pg_hba.conf — who may connect and how they authenticate; pg_ident.conf — OS-user to role mapping.
2. Why does sudo -u postgres psql work without a password on Linux, and what is that mechanism called? Peer authentication — the OS identity proves the role locally, per pg_hba.conf.
3. What does mysql_secure_installation do, and why is it run immediately after install? Sets root password, removes anonymous users, disallows remote root, drops test data — closing the fresh-install attack surface (Chapter 20).
4. Contrast psql and mysql meta-commands with two examples each. *psql: \dt lists tables, \i file.sql runs a script; mysql: SHOW TABLES; lists, SOURCE file.sql runs — backslash escapes vs SQL-statement style.*
5. What five facts does every database connection supply, and name three formats for them. Host, port, database, user, password; CLI flags, environment variables/connection URI, driver URLs (JDBC/DSN).
6. Why are 'registrar'@'localhost' and 'registrar'@'10.0.0.5' different accounts in MySQL? MySQL identities are user@host pairs — each may have its own password and privileges; host patterns (%, 10.0.0.%) wildcard them.
7. What two changes must accompany listen_addresses = '*' before a remote PostgreSQL connection works? A pg_hba.conf host line for the user/source with an auth method, and an open port through the firewall.
8. A driver fails with "authentication plugin ... cannot be loaded" while the mysql client works. Diagnose. MySQL 8's caching_sha2_password versus the older client library — update the driver or re-create the account with mysql_native_password.
9. Match each symptom to its ladder rung: connection refused; access denied for user; too many connections. Refused — network/service; access denied — authorization (grants/hosts); too many — server capacity/pooling (rung five).
10. Write the commands to run schema.sql non-interactively on both platforms. *psql -U registrar -d university -f schema.sql and mysql -u registrar -p university < schema.sql (or mysql ... -e "SOURCE schema.sql").*
11. Which GUI feature belongs to Workbench alone, and which pgAdmin feature wraps pg_dump? Workbench's EER diagram editor (reverse/forward engineering); pgAdmin's backup dialogs wrap pg_dump/pg_restore.
12. Why work as registrar rather than postgres/root from the first day? Least privilege practiced early: superuser accidents are unbounded, and applications configured with superuser credentials are the classic breach amplifier.

Mini-Project

Produce your laboratory's environment document, ENVIRONMENT.md: the exact install routes you took on your OS; versions and service names of both servers; the data directories and configuration file paths; the registrar account details (and why the password is not in the document — state where it lives instead); the five working connection recipes (flags, env/URI, config file, GUI, script); the six-count verification output from both platforms; and your troubleshooting log — at least three deliberately broken connections (wrong port, wrong password, wrong database), each with the exact error text and its ladder rung. Finish with the connection ladder as a diagram. This document is the front matter of every subsequent laboratory and the disaster-recovery starting point of Chapter 21's exercises.