GNet Solutions » GNU/Linux Administration

Support #553

Updated by Daniel Curtis over 8 years ago

This is a simple guide for setting up a standalone PostgreSQL database server on Debian. 

 * Use sudo to get a root shell 
 <pre> 
 sudo -s 
 </pre> 

 * Update the system: 
 <pre> 
 apt-get update && sudo apt-get upgrade 
 </pre> 

 h1. Install PostgreSQL 

 * Install PostgreSQL 
 <pre> 
 apt-get install postgresql postgresql-client 
 </pre> 

 h2. Configure PostgreSQL 

 * Edit pg_hba.conf to enable Create a database access via the local socket: 
 <pre> 
 nano /etc/postgresql/9.4/main/pg_hba.conf 
 </pre> 
 #* Replace peer with *trust* 
 <pre> 
 local     all           all                                 trust    
 </pre>  

 * Edit the main postgresql config: 
 <pre> 
 nano /etc/postgresql/9.4/main/postgresql.conf 
 </pre> 
 #* And change the @listen_addresses@ parameter: 
 <pre> 
 listen_addresses = '*' 
 </pre> and user 

 * Then append Create a rule regular system user account using adduser (skip this step to enable network access from a given subnet: use an existing account): 
 <pre> 
 echo "host      all       all       192.168.0.0/24    trust" >> /etc/postgresql/9.4/main/pg_hba.conf adduser somepguser 
 </pre> 

 * And restart postgresql: 
 <pre> 
 service postgresql restart 
 </pre> 

 h2. Create a database and user 

 * Connect to the default database: 
 <pre> 
 su - postgres 
 psql 
 </pre> 
 #* Set a password for the postgres user: 
 <pre> 
 \password postgres 
 </pre> 
 #* Create a new user, the The postgres admin console will show @postgres=#@: @postgres=#@, create a new user: 
 <pre> 
 CREATE USER somepguser WITH PASSWORD 'somepguserpass'; 
 </pre> 
 #* And a create a database: 
 <pre> 
 CREATE DATABASE somepgdatabase OWNER somepguser; 
 </pre> 
 #* Quit from the database 
 <pre> 
 \q 
 </pre> 

 * Exit from the postgres user 
 <pre> 
 exit 
 </pre> 

 * Connect as somepguser to the new database: 
 <pre> 
 su - somepguser 
 psql -d somepgdatabase -U somepguser 
 </pre> 

 h3. Enable Network Service 

 * Edit the main postgresql config: 
 <pre> 
 nano /etc/postgresql/9.4/main/postgresql.conf 
 </pre> 
 #* If connecting And change the @listen_addresses@ parameter: 
 <pre> 
 listen_addresses = '*' 
 </pre> 

 * Then append a rule to allow access from a remote host, connect using the following: given subnet: 
 <pre> 
 psql -h pg.example.com -d somepgdatabase -U somepguser echo "host      all       all       192.168.0.0/24    trust" >> /etc/postgresql/9.4/main/pg_hba.conf 
 </pre> 

 * And restart postgresql: 
 <pre> 
 service postgresql restart 
 </pre> 

 h2. Resources 

 * https://wiki.debian.org/PostgreSql

Back