PostgreSQL installation and setup - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding PostgreSQL Service Status

After installing PostgreSQL on a Linux system, which command correctly checks if the PostgreSQL service is running?

A pg_ctl check

B psql --status

C service postgresql start

D systemctl status postgresql

💡 Hint

Think about how services are managed on modern Linux systems.

The 'systemctl status postgresql' command shows the current status of the PostgreSQL service. 'psql --status' is invalid, 'service postgresql start' starts the service instead of checking status, and 'pg_ctl check' is not a valid command.

❓ query_result

intermediate

Default PostgreSQL Port Number

What is the default port number PostgreSQL listens on after installation?

A 5432

B 3306

C 1521

D 8080

💡 Hint

This port is commonly used by PostgreSQL worldwide.

PostgreSQL uses port 5432 by default. Port 3306 is for MySQL, 1521 for Oracle, and 8080 is a common HTTP alternative port.

📝 Syntax

advanced

Correct Command to Initialize a New PostgreSQL Database Cluster

Which of the following commands correctly initializes a new PostgreSQL database cluster on a Linux system?

A pg_init -D /var/lib/postgresql/data

B initdb -D /var/lib/postgresql/data

C createdb -D /var/lib/postgresql/data

D pg_createcluster -D /var/lib/postgresql/data

💡 Hint

The command name starts with 'init'.

'initdb' initializes a new database cluster. 'pg_init' is invalid, 'pg_createcluster' is a valid Debian/Ubuntu tool but does not use '-D' option, and 'createdb' creates a database inside an existing cluster.

🔧 Debug

advanced

Troubleshooting PostgreSQL Connection Refused Error

After installing PostgreSQL and starting the service, a user tries to connect with 'psql' but gets 'connection refused'. Which is the most likely cause?

A The 'psql' command is misspelled

B The PostgreSQL data directory is empty

C PostgreSQL is not listening on the expected port or address

D The database user password is incorrect

💡 Hint

Connection refused means the server is not accepting connections on the network.

If PostgreSQL is not configured to listen on the correct port or network address, connection attempts will be refused. Misspelling 'psql' causes command not found, wrong password causes authentication failure, and empty data directory causes startup failure.

❓ optimization

expert

Optimizing PostgreSQL Startup Performance

Which configuration change can improve PostgreSQL startup time after installation on a server with limited memory?

A Reduce 'shared_buffers' size to a smaller value

B Set 'work_mem' to a very large value

C Enable 'fsync' to off

D Increase 'max_connections' to a very high number

💡 Hint

Startup time can be affected by memory allocation settings.

'shared_buffers' controls memory used for caching data. Reducing it can lower memory pressure and speed startup on limited memory systems. Increasing 'max_connections' or 'work_mem' uses more memory, potentially slowing startup. Disabling 'fsync' risks data integrity.