ALTER DATABASE

ADVERTISEMENT

--ALTER DATABASE

--STEP 1: Connect to PostgreSQL on Amazon RDS using psql from EC2
psql --host=endpoint/connection string --port=5432 --dbname=postgres --username=postgres

-- '-d postgres' means you are connected to the default 'postgres' database
-- This is important because you cannot rename the database you're currently using

-- STEP 2: Create a test database that we will modify
CREATE DATABASE testdb;

-- This creates a new database named 'testdb'

-- STEP 3: Rename the database
-- NOTE: You cannot rename the database you're currently connected to.
-- Make sure you're connected to a different one (e.g., 'postgres')

-- ✅ You are already connected to 'postgres' so you're safe to run this:
ALTER DATABASE testdb
RENAME TO testhr;

SELECT
    d.datname AS database_name,
    pg_catalog.pg_get_userbyid(d.datdba) AS owner,
    d.encoding,
    pg_encoding_to_char(d.encoding) AS encoding_name,
    d.datcollate AS lc_collate,
    d.datctype AS lc_ctype,
    t.spcname AS tablespace,
    d.datallowconn AS allow_connections,
    d.datconnlimit AS connection_limit,
    d.datistemplate AS is_template
FROM
    pg_database d
LEFT JOIN
    pg_tablespace t ON d.dattablespace = t.oid
WHERE
    d.datname = 'testhr';


-- This renames 'testdb2' to 'testhr'

-- If you had been connected to 'testdb', you would first exit and reconnect like this:
-- \q               ← exit from psql
-- Then run:
-- psql --host=database-1.cxeoacy8ksy4.ap-south-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=hr1

-- STEP 4: Create a new role if needed
CREATE ROLE hr1
LOGIN
CREATEDB
PASSWORD 'securePa$$1';

-- Creates a role 'hr' that can log in and create databases

-- STEP 5: Change the owner of the renamed database
ALTER DATABASE testhr
OWNER TO hr1;

-- Changes the owner from 'postgres' to 'hr'

-- STEP 6: Set connection limit for the database
ALTER DATABASE testhr
WITH CONNECTION LIMIT 50;

-- Limits max concurrent connections to 50


-- STEP 7: Set a config value specific to this database
ALTER DATABASE testhr
SET escape_string_warning = off;

-- This setting will be applied every time someone connects to 'testhrdb'

-- STEP 8: View any DB-level configuration overrides
SELECT * FROM pg_db_role_setting;

-- Shows runtime config changes made via ALTER DATABASE SET

-- STEP 9: Reset the config setting if needed
ALTER DATABASE testhr
RESET escape_string_warning;

-- Removes the override and uses the postgresql.conf value instead

-- STEP 10: View all databases (safe for Amazon RDS)
SELECT
    d.datname AS database_name,
    pg_catalog.pg_get_userbyid(d.datdba) AS owner,
    d.encoding,
    pg_encoding_to_char(d.encoding) AS encoding_name,
    d.datcollate AS lc_collate,
    d.datctype AS lc_ctype,
    t.spcname AS tablespace,
    d.datallowconn AS allow_connections,
    d.datconnlimit AS connection_limit,
    d.datistemplate AS is_template
FROM
    pg_database d
LEFT JOIN
    pg_tablespace t ON d.dattablespace = t.oid
WHERE
    d.datname = 'testhr';

-- Safer alternative to \l on Amazon RDS

-- STEP 11: Exit psql
\q

ADVERTISEMENT