I'm currently working on a project where I'm responsible for installing PostgreSQL 17 on over 250 Windows servers. Initially, I was manually logging into each server, installing PostgreSQL, and creating the necessary databases, schemas, and roles. Since I'm not very familiar with automating tasks via batch files in Windows, the process quickly became tedious and time-consuming.
While I was slogging through the manual steps, a fellow developer noticed my frustration and suggested a smarter approach. Here's a light-hearted re-creation of our conversation:
Idiot DBA: (In a dimly lit server room, I was hunching over a keyboard, surrounded by 250 sticky notes labeled "TODO".)
"God demn it! At this rate, I'll finish setting up PostgreSQL on all 250 servers by the time PostgreSQL 18 is released!"
Smart Developer: "Hey there, you look stressed. You look like a warrior of the keyboard. What's eating you?
Idiot DBA: "I'm setting up PostgreSQL—installing it, creating databases and schemas on every single server manually."
Smart Developer: "You're doing all of that manually? Are you a tech archaeologist? You must be an idiot! There's a better way, you know!"
Idiot DBA: "You call me an idiot? On the contrary, I'm a top-notch PostgreSQL DBA. Look at my fingers - they are lightning-fast. You think you can do this better than me?"
Smart Developer: "Yeah, I heared the rumor that you are the best DBA in Korea, but now your fingers are being cursed by Ctrl+C and Ctrl+V. Listen, MacGyver, in this AI era, there is this thing called automation. You should try it.
Idiot DBA: "Automation? You think this task can be automated?"
Smart Developer: Yeah, just a humble little batch file. One click, and bam-databases, schemas, users - all done.
The developer changed everything. He created a Windows batch file that automates the entire setup process—including creating a PostgreSQL database, roles, schemas, and configuring permissions. All I have to do now is double-click the .bat file on each server.
* PostgreSQL Setup Batch script (Windows)
Save the script below as create_db.bat. Running it will automatically:
- Create the database analdb
- Create four users and schemas
- Configure permissions and extensions
@echo off
SET PGPATH="C:\Program Files\PostgreSQL\17\bin\psql.exe"
SET PGUSER=postgres
SET PGPASSWORD=postgres
SET DB_NAME=analdb
SET DB_USER1=scott_user
SET DB_PASSWORD1=tiger
SET SCHEMA_NAME1=scott
SET DB_USER2=tom_user
SET DB_PASSWORD2=cat
SET SCHEMA_NAME2=tom
SET DB_USER3=jane_user
SET DB_PASSWORD3=elephant
SET SCHEMA_NAME3=jane
SET DB_USER4=james_user
SET DB_PASSWORD4=abdkjfkd
SET SCHEMA_NAME4=james
SET SQL_FILE=DB_CREATE.sql
echo -- Creating Database, Roles, Schemas and Permissions > %SQL_FILE%
REM 0. Change the password encryption method
REM echo ALTER SYSTEM SET password_encryption = 'scram-sha-256';
:: 1. Create Roles
echo CREATE ROLE %DB_USER1% WITH LOGIN PASSWORD '%DB_PASSWORD1%'; >> %SQL_FILE%
echo CREATE ROLE %DB_USER2% WITH LOGIN PASSWORD '%DB_PASSWORD2%'; >> %SQL_FILE%
echo CREATE ROLE %DB_USER3% WITH LOGIN PASSWORD '%DB_PASSWORD3%'; >> %SQL_FILE%
echo CREATE ROLE %DB_USER4% WITH LOGIN PASSWORD '%DB_PASSWORD4%'; >> %SQL_FILE%
:: 2. Create Database
echo CREATE DATABASE %DB_NAME% lc_collate='C' lc_ctype='ko_KR.UTF8' template template0 OWNER %DB_USER1%; >> %SQL_FILE%
:: 3. Create Schemas
echo \connect %DB_NAME% >> %SQL_FILE%
echo CREATE SCHEMA %SCHEMA_NAME1% AUTHORIZATION %DB_USER1%; >> %SQL_FILE%
echo CREATE SCHEMA %SCHEMA_NAME2% AUTHORIZATION %DB_USER2%; >> %SQL_FILE%
echo CREATE SCHEMA %SCHEMA_NAME3% AUTHORIZATION %DB_USER3%; >> %SQL_FILE%
echo CREATE SCHEMA %SCHEMA_NAME4% AUTHORIZATION %DB_USER4%; >> %SQL_FILE%
:: 4. Set search_path
echo ALTER USER %DB_USER1% SET search_path TO %SCHEMA_NAME1%; >> %SQL_FILE%
echo ALTER USER %DB_USER2% SET search_path TO %SCHEMA_NAME2%; >> %SQL_FILE%
echo ALTER USER %DB_USER3% SET search_path TO %SCHEMA_NAME3%; >> %SQL_FILE%
echo ALTER USER %DB_USER4% SET search_path TO %SCHEMA_NAME4%; >> %SQL_FILE%
:: 5. Grant CREATE on Database
echo GRANT CREATE ON DATABASE %DB_NAME% TO %DB_USER1%; >> %SQL_FILE%
echo GRANT CREATE ON DATABASE %DB_NAME% TO %DB_USER2%; >> %SQL_FILE%
echo GRANT CREATE ON DATABASE %DB_NAME% TO %DB_USER3%; >> %SQL_FILE%
echo GRANT CREATE ON DATABASE %DB_NAME% TO %DB_USER4%; >> %SQL_FILE%
:: 6. Revoke Public Privileges
echo REVOKE ALL PRIVILEGES ON SCHEMA %SCHEMA_NAME1% FROM PUBLIC; >> %SQL_FILE%
echo REVOKE ALL PRIVILEGES ON SCHEMA %SCHEMA_NAME2% FROM PUBLIC; >> %SQL_FILE%
echo REVOKE ALL PRIVILEGES ON SCHEMA %SCHEMA_NAME3% FROM PUBLIC; >> %SQL_FILE%
echo REVOKE ALL PRIVILEGES ON SCHEMA %SCHEMA_NAME4% FROM PUBLIC; >> %SQL_FILE%
:: 7. Create Extensions
echo CREATE EXTENSION pgcrypto SCHEMA %SCHEMA_NAME1%; >> %SQL_FILE%
echo CREATE EXTENSION pgcrypto SCHEMA %SCHEMA_NAME2%; >> %SQL_FILE%
echo CREATE EXTENSION pgcrypto SCHEMA %SCHEMA_NAME3%; >> %SQL_FILE%
echo CREATE EXTENSION pgcrypto SCHEMA %SCHEMA_NAME4%; >> %SQL_FILE%
:: Execute SQL
%PGPATH% -U %PGUSER% password=%PGPASSWORD% -p 5432 -d postgres -f %SQL_FILE%
IF %ERRORLEVEL% EQU 0 (
echo PostgreSQL setup completed successfully!
) ELSE (
echo Failed to set up PostgreSQL. Check the SQL script and connection.
)
:: Optional: Delete temp file
REM DEL %SQL_FILE%
Conclusion
This little automation trick saved me hours of repetitive work. If you're ever in a situation where you have to configure databases on multiple machines, always think automation first. A few lines of code can make your job significantly easier.
