Skip to main content

Launching PostgreSQL

Learn how to launch and deploy PostgreSQL database instances on KloudBean. KloudBean provides managed PostgreSQL database with high performance and robust security policies.

Overview​

KloudBean provides managed PostgreSQL database. KloudBean databases are high performant and having good security policies as well.

KloudBean Managed PostgreSQL Benefits​

High Performance:

  • Optimized Configuration: Pre-configured for optimal performance
  • Fast Query Execution: Advanced query optimizer and execution engine
  • Efficient Resource Usage: Smart resource allocation
  • Scalable Architecture: Built to scale with your needs
  • Advanced Features: JSON support, full-text search, and advanced indexing

Security Policies:

  • Encrypted Connections: SSL/TLS encryption for secure connections
  • Access Control: Granular access control and IP whitelisting
  • Regular Updates: Automatic security updates and patches
  • Backup Security: Secure backup storage and encryption

Easy Setup and Access:

  • Few Steps Installation: It is a few steps installation process
  • Quick Deployment: Database creation takes only 2-5 minutes
  • Simple Configuration: Minimal configuration required
  • Easy Access: Straightforward access to database credentials
  • User-Friendly Interface: Intuitive dashboard for management

Additional Benefits:

  • Automated Backups: Automatic backup scheduling and management
  • High Availability: Built-in redundancy and failover
  • Monitoring: Real-time performance monitoring
  • Scalability: Easy scaling as your needs grow
  • Support: Expert support from KloudBean team
  • Extensions: Support for PostgreSQL extensions (PostGIS, pg_trgm, etc.)

Prerequisites​

  • An active KloudBean account
  • Understanding of your database requirements
  • Payment method for database provisioning

Launching PostgreSQL Database​

Step 1: Navigate to Database Section​

In order to launch PostgreSQL database:

From your KloudBean dashboard navigate to Managed database or from top header click on "DBS".

On DBS page there is a button "Launch Database".

Database Step 1

Once you click it will take you to database provision page.

Step 2: Select Database Engine​

Where you have to select:

Database engine: Select PostgreSQL from here.

PostgreSQL Launch Step 1

Step 3: Provide Database Details​

Next step is to provide following database detail:

PostgreSQL Launch Step 2

Required Information:

  1. Database Name:

    • Enter a unique name for your database
    • Use lowercase letters, numbers, and underscores
    • Example: myapp_db, production_db

  2. Database User:

    • Create a username for database access
    • Use lowercase letters, numbers, and underscores
    • Example: myapp_user, db_admin

  3. Database Version:

    • Select PostgreSQL version:
      • PostgreSQL 16 (latest, recommended)
      • PostgreSQL 15 (stable)
      • PostgreSQL 14 (legacy support)
    • Choose based on your application requirements

  4. Server Location:

    • Choose the geographic location for your database
    • Select region closest to your application servers
    • Consider data residency requirements

Step 4: Select Server Size​

Once these details are added:

Decide server size and select most suitable size. Note that you can scale your database up any time, contact KloudBean for that.

Server Size Options:

  • Small: Suitable for development and testing
  • Medium: Good for small to medium applications
  • Large: For production applications with high traffic
  • Custom: Custom configuration for specific needs

Scaling Options:

  • Vertical Scaling: Increase CPU, RAM, or storage
  • Horizontal Scaling: Add read replicas
  • Contact Support: Reach out to KloudBean support for scaling assistance

Considerations:

  • Current Needs: Start with size that meets current needs
  • Growth Projection: Consider future growth when selecting size
  • Cost Optimization: Start smaller and scale as needed
  • Performance: Larger sizes offer better performance

Step 5: Launch Database​

Click on "Launch now" button, it will take you to the payment screen, where you will complete your payment.

Payment Process:

  1. Review Configuration: Verify all settings are correct
  2. Payment Method: Select your payment method
  3. Complete Payment: Complete the payment process
  4. Confirmation: Receive confirmation of payment

Once payment is completed, your database creation process will be initiated, and it will take around 2-5 minutes to create and configure your high performance database.

Creation Process:

  • Database instance provisioning
  • PostgreSQL installation and configuration
  • Security setup and encryption
  • Network configuration
  • Initial backup setup

Once created you will see your database in DBS page.

PostgreSQL Access Step 1

Accessing Database Details​

Step 1: Navigate to Database Administration​

In order to get database access detail click on your database and it will take you to database administration page where all the database access information is listed.

PostgreSQL Access Step 2

Step 2: View Database Credentials​

Database Access Information:

Host:

  • Database server hostname or IP address
  • Use this to connect to your database
  • Example: postgres-123456.kloudbeansite.com or 192.168.1.100

Port:

  • PostgreSQL port number (usually 5432)
  • Default PostgreSQL port for connections
  • Example: 5432

Database Name:

  • Name of your database
  • Use this in connection strings
  • Example: myapp_db

Master User:

  • Username for database access
  • Primary database user credentials
  • Example: myapp_user

Database Password:

  • Password for database authentication
  • Keep this secure and never expose publicly
  • Use this with Master User for authentication

IP Address:

  • Database server IP address
  • Use for IP whitelisting if needed
  • Example: 192.168.1.100

You can use this information to use this database in your application.

Accessing Database​

Database Access Status​

By default, the public access to your database is disabled.

In order to check access status, navigate to "Database Specifications" section of "Access" tab.

Database Public Access

This represents that access is disabled and you cannot access it.

Enabling Database Access​

In order to access database you have to whitelist the IP address of your source server or you have to enable public access of your database.

Important: Do Not Enable Public Access Unnecessarily

Why Public Access Should Be Avoided:

  • Security Risk: Public access exposes your database to the entire internet
  • Attack Surface: Increases vulnerability to attacks and unauthorized access
  • Compliance Issues: May violate data protection regulations
  • Best Practice: Always use IP whitelisting instead of public access

When Public Access Might Be Needed:

  • Development/Testing: Only for non-production environments
  • Temporary Access: For specific, time-limited tasks
  • With Additional Security: Combined with strong passwords and SSL

Recommended Approach:

  • IP Whitelisting: Always prefer IP whitelisting over public access
  • Specific IPs: Whitelist only specific IP addresses that need access
  • Source Server IP: Whitelist your application server's IP address
  • VPN Access: Consider VPN for additional security layer

To get to know how you can update database access, read document: Controlling Database Access

Once access is enabled, now you can access your database.

Accessing Database from pgAdmin​

Step 1: Install pgAdmin​

Download and Install:

  1. Download: Go to pgAdmin Download Page
  2. Install: Follow installation instructions for your operating system
  3. Launch: Open pgAdmin

Step 2: Create New Server Connection​

  1. Right-click "Servers" in the left panel
  2. Select "Register" → "Server"
  3. Fill in General Tab:
    • Name: Give a descriptive name (e.g., "KloudBean PostgreSQL")
  4. Fill in Connection Tab:
    • Host name/address: Enter the Host from database credentials
    • Port: Enter the Port (usually 5432)
    • Maintenance database: Enter Database Name
    • Username: Enter the Master User from credentials
    • Password: Enter password (or save in password file)
  5. Click "Save"

Step 3: Connect to Database​

  1. Expand your server in the left panel
  2. Expand "Databases"
  3. Click on your database name
  4. You're Connected: You should now see the database schema and tables

Step 4: Using pgAdmin​

Common Operations:

  • Browse Data: Right-click tables → "View/Edit Data"
  • Run Queries: Use Query Tool to run SQL queries
  • Create Tables: Right-click "Tables" → "Create" → "Table"
  • Import/Export: Use Import/Export tools
  • Manage Users: Right-click "Login/Group Roles" → "Create"
  • View Schemas: Browse database structure

Accessing Database from Shell/Command Line​

Step 1: Install PostgreSQL Client​

On Linux (Ubuntu/Debian):

sudo apt update
sudo apt install postgresql-client

On macOS:

brew install postgresql

On Windows:

Step 2: Connect to Database​

Basic Connection Command:

psql -h [HOST] -p [PORT] -U [USERNAME] -d [DATABASE_NAME]

Example:

psql -h postgres-123456.kloudbeansite.com -p 5432 -U myapp_user -d myapp_db

You will be prompted to enter password.

Alternative: Include Password in Connection String:

PGPASSWORD=your_password psql -h [HOST] -p [PORT] -U [USERNAME] -d [DATABASE_NAME]

Step 3: Basic SQL Commands​

Once connected, you can run SQL commands:

List Databases:

\l
-- or
SELECT datname FROM pg_database;

Connect to Database:

\c database_name

List Tables:

\dt
-- or
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Describe Table:

\d table_name
-- or
\d+ table_name

Select Data:

SELECT * FROM table_name;
SELECT column1, column2 FROM table_name WHERE condition;

Create Table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Data:

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

Update Data:

UPDATE users SET email = '[email protected]' WHERE id = 1;

Delete Data:

DELETE FROM users WHERE id = 1;

Create Index:

CREATE INDEX idx_username ON users(username);

Show Table Structure:

\d table_name

Exit psql:

\q
-- or
EXIT;

Step 4: Running SQL Scripts​

Execute SQL File:

psql -h [HOST] -p [PORT] -U [USERNAME] -d [DATABASE_NAME] -f script.sql

Example:

psql -h postgres-123456.kloudbeansite.com -p 5432 -U myapp_user -d myapp_db -f backup.sql

Execute SQL Commands:

psql -h [HOST] -p [PORT] -U [USERNAME] -d [DATABASE_NAME] -c "SELECT * FROM users;"

Using Database Credentials in Your Application​

Connection String Format​

Standard PostgreSQL Connection String:

postgresql://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DATABASE_NAME]

Example:

postgresql://myapp_user:[email protected]:5432/myapp_db

PHP (PDO)​

Using PDO:

<?php
$host = 'postgres-123456.kloudbeansite.com';
$port = 5432;
$dbname = 'myapp_db';
$username = 'myapp_user';
$password = 'your_password';

try {
    $pdo = new PDO(
        "pgsql:host=$host;port=$port;dbname=$dbname",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Node.js​

Using pg package:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'postgres-123456.kloudbeansite.com',
  port: 5432,
  user: 'myapp_user',
  password: 'your_password',
  database: 'myapp_db',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Use pool
const result = await pool.query('SELECT * FROM users');
console.log(result.rows);

Using connection string:

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'postgresql://myapp_user:[email protected]:5432/myapp_db'
});

Python​

Using psycopg2:

import psycopg2

conn = psycopg2.connect(
    host='postgres-123456.kloudbeansite.com',
    port=5432,
    user='myapp_user',
    password='your_password',
    database='myapp_db'
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

Using SQLAlchemy:

from sqlalchemy import create_engine

connection_string = "postgresql://myapp_user:[email protected]:5432/myapp_db"
engine = create_engine(connection_string)

# Use engine for queries
with engine.connect() as conn:
    result = conn.execute("SELECT * FROM users")
    for row in result:
        print(row)

Best Practices​

Security​

  • Strong Passwords: Use strong, unique passwords for database users
  • IP Whitelisting: Always use IP whitelisting instead of public access
  • SSL/TLS: Enable SSL/TLS encryption for all connections
  • Regular Updates: Keep PostgreSQL updated to latest version
  • Access Control: Implement proper user permissions and access control
  • Backup Security: Secure your database backups

Performance​

  • Proper Indexing: Create indexes on frequently queried columns
  • Query Optimization: Optimize your SQL queries
  • Connection Pooling: Use connection pooling in applications
  • Resource Monitoring: Monitor database performance regularly
  • Regular Maintenance: Perform VACUUM and ANALYZE regularly
  • Extensions: Use appropriate PostgreSQL extensions

Management​

  • Regular Backups: Enable and test automatic backups
  • Monitor Usage: Monitor database usage and performance
  • Scale When Needed: Scale your database as your needs grow
  • Documentation: Document your database configuration
  • Version Control: Keep track of schema changes

Troubleshooting​

Connection Issues​

Cannot Connect to Database:

  • Check IP Whitelist: Verify your IP is whitelisted
  • Check Credentials: Verify host, port, username, and password
  • Check Network: Verify network connectivity
  • Check Firewall: Check firewall rules
  • Review Access Settings: Check database access configuration

Access Denied Errors:

  • Verify Credentials: Check username and password
  • Check User Permissions: Verify user has necessary permissions
  • Check Host Restrictions: Verify pg_hba.conf settings
  • Check Database Access: Verify user has access to the database

Performance Issues​

Slow Queries:

  • Check Indexes: Verify proper indexes exist
  • Optimize Queries: Review and optimize slow queries
  • Check Resources: Monitor CPU, memory, and disk usage
  • Review Configuration: Review PostgreSQL configuration
  • Run VACUUM: Perform VACUUM and ANALYZE

Connection Limits:

  • Check Limits: Verify connection limits
  • Close Idle Connections: Close unused connections
  • Use Connection Pooling: Implement connection pooling
  • Review Active Connections: Check for connection leaks

Next Steps​

After launching your PostgreSQL database: