Configuration

Stop / start PostgreSQL service

service postgresql stop

// Stops PostgreSQL service through root user (Linux)

service postgresql start

// Starts PostgreSQL service through root user (Linux)

service postgresql restart

// Restarts PostgreSQL service through root user (Linux)

If running from non root user you must prefix your command with “sudo” and non-root user should already be there in sudoer’s list. Also be careful with running these commands because some distributors don’t provide them these days.

Display configuration parameters

show all

// List all current runtime configuration parameters (psql)

Display configuration parameters using sql

select * from pg_settings;

// List all current runtime configuration parameters using SQL with additional details, including description (SQL)

Show current setting from “max_connections”

SELECT current_setting('max_connections'); current_setting ----------------- 100 (1 row)

// Display current value set for “max_connections” parameter (SQL)

Show Postgres config file location

show config_file; config_file ------------------------------------------ /etc/postgresql/9.6/main/postgresql.conf (1 row)

// Show PostgreSQL configuration file location (psql)

The PostgreSQL configuration files are stored in directory from above command output. The main configuration file is called “postgresql.conf“.

Display contents of Postgres config file location

postgres@localhost:~$ less /etc/postgresql/9.6/main/postgresql.conf . . . . data_directory = '/var/lib/postgresql/9.6/main' # use data in another directory # (change requires restart) hba_file = '/etc/postgresql/9.6/main/pg_hba.conf' # host-based authentication file # (change requires restart) ident_file = '/etc/postgresql/9.6/main/pg_ident.conf' # ident configuration file # (change requires restart) listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) . . . .

(Linux)

– data_directory directive tells where the database files are stored.

– hba_file directive tells the host based authentication file.

– port directive tells the TCP port number. The default is 5432.