Restricting new connections to a PostgreSQL database is essential during maintenance periods or to manage system resources effectively. Here are several methods to achieve this:

1. Modify max_connections Setting

The max_connections setting in the postgresql.conf file specifies the maximum number of concurrent connections to the PostgreSQL server. Reducing this number can limit new connections, but it requires a server restart to take effect. This approach is not ideal for temporary restrictions as it affects all users and requires downtime.

max_connections = 'desired_number_of_connections'

After changing this setting, restart the PostgreSQL server for the changes to take effect.

2. Use pgBouncer or Another Connection Pooler

pgBouncer is a popular connection pooler for PostgreSQL that manages a pool of connections. You can configure pgBouncer to limit the number of connections to PostgreSQL without modifying the database configuration directly. This method is more flexible than changing max_connections and doesn't require restarting the PostgreSQL server.

In pgBouncer, adjust the max_client_conn and default_pool_size settings to control the total number of allowed connections and the number of connections per user/database.

3. Update pg_hba.conf for Connection Control

The pg_hba.conf file controls which hosts are allowed to connect to the database, the authentication methods, and the databases they can access. You can temporarily restrict new connections by modifying rules in pg_hba.conf. For example, to deny all connections except from specific IP addresses, you could add:

# Deny all other connections
host    all             all             0.0.0.0/0               reject

Make sure to reload the PostgreSQL configuration after modifying pg_hba.conf:

pg_ctl reload

4. Temporarily Disable a Particular User or Database

To restrict connections for a specific user or database, you can alter the user or database to disallow connections. For a user:

ALTER ROLE username NOLOGIN;

For a database:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'dbname';

5. Use Administrative Commands

PostgreSQL includes administrative functions like pg_terminate_backend(pid) to forcefully disconnect sessions. You can use this in combination with monitoring queries to prevent new connections:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'dbname' AND pid <> pg_backend_pid();

This method is more aggressive and should be used with caution, as it will immediately terminate active connections.

Conclusion

Choosing the right method to restrict connections depends on your specific needs, such as whether the restriction is temporary or permanent, and whether it applies to all users or just specific databases or users. Always plan and test changes in a staging environment before applying them to production to understand their impact.