Connecting to PostgreSQL Databases
PostgreSQL is an open-source object-relational database management system.
This document shows how to connect to your PostgreSQL databases on Opalstack from clients and applications that use PostgreSQL databases.
The parameters used to connect to a PostgreSQL database on your Opalstack server are:
-
Host:
localhost
-
Port:
5432
- Database name: your PostgreSQL database name
- Database user name: your PostgreSQL database user name
- Database user password: your PostgreSQL database user password
Note that many applications use a default configuration with the host as localhost
and the port as 5432
as mentioned above so those parameters might be optional or not present in your application settings. Consult your application's documentation for its specific configuration requirements.
The following examples use mydb
as the PostgreSQL database name, mydbuser
as the database user name, and mydbpassword
as the database user password. Replace them with your own database name and credentials.
Command line client
First log in to a SSH session as a shell user on your server, then...
psql -U mydbuser mydb
...and enter your database user password when prompted.
Python
First install the psycopg2
library in your environment...
export PATH=/usr/pgsql-11/bin/:$PATH
cd ~/apps/appname
source env/bin/activate
pip install psycopg2
... then in your Python code:
import psycopg2
conn = psycopg2.connect(host='localhost', user='mydbuser',
password='mydbpassword', dbname='mydb')
PHP
In your PHP code:
$conn = pg_connect("host=localhost dbname=mydb user=mydbuser password=mydbpassword")
Javascript
First install the pg
package into your Node.js project directory...
export PATH=/usr/pgsql-11/bin/:$PATH
npm install pg
... then in your Javascript code:
var pg = require('pg');
var conn = new pg.Client({
user: 'mydbuser',
host: 'localhost',
database: 'mydb',
password: 'mydbpassword',
port: 5432,
})
conn.connect()
Ruby
First install the pg
gem...
export PATH=/usr/pgsql-11/bin/:$PATH
gem install pg
... then in your Ruby code:
require 'pg'
conn = PGconn.connect(:host => 'localhost', :port => 5432, :dbname => 'mydb',
:user => 'mydbuser', :password => 'mydbpassword' )
Opalstack requires the use of a SSH tunnel for remote connections to PostgreSQL databases.
- 1
Create a PostgreSQL database and user.
- 2
Create a shell user.
- 3
On the client machine making the remote connection (for example, your own computer) open your terminal application and run the following command to establish the tunnel, replacing
myuser@opalN.opalstack.com
with your shell username and Opalstack server hostname:ssh myuser@opalN.opalstack.com -L 5432:127.0.0.1:5432 -N
Note:
If you have a PostgreSQL server already running on the client machine, then you will need to change the local port used by the tunnel. The following example uses port 5433 as the local port:
ssh myuser@opalN.opalstack.com -L 5433:127.0.0.1:5432 -N
If you're using PuTTY for your SSH connections, use
5432
as the source port and127.0.0.1:5432
as the destination under "Category > Connection > SSH > Tunnels".
- 4
Finally, on the client machine configure your application to connect to your database with the following parameters:
-
Host:
127.0.0.1
-
Port:
5432
(or whatever your local port is) - Database name: your PostgreSQL database name
- Database user name: your PostgreSQL database user name
- Database user password: your PostgreSQL database user password
-
Host:
Your Opalstack service includes a web-based database administration tool called Adminer which is similar to phpPgAdmin.
To access Adminer:
- 1
Go to
https://SERVERNAME.opalstacked.com/adminer/
in your browser, replacingSERVERNAME
with your Opalstack server name (for example "opal1" or "vps2").- 2
Complete the login form as follows:
- Server: PostgreSQL
- Username: your PostgreSQL user name
- Password: your PostgreSQL user password
- Database: your PostgreSQL database name.
- 3
Click the Login button to log in to your database with the credentials you provided in step 2.