Download PostgreSQL binnary
https://www.enterprisedb.com/download-postgresql-binaries
PostgreSQL configuration
https://git-maspaad.blogspot.com/2020/12/how-to-start-stop-and-check-status.html
https://git-maspaad.blogspot.com/2024/11/tutorial-install-postgresql-on-linux.html
- - - - - - - - - - - - - - - - - - - - - - - - -
InitDB (first time only)
$ pg_ctl initdb -D "..\data" (enter)
Register PostgreSQL as windows service
$ pg_ctl register -N PostgreSQL-v9-p5433 -D "..\data"
Create user "postgres" (first time only)
$ createuser --port=5433 -s postgres
https://stackoverflow.com/questions/15301826/psql-fatal-role-postgres-does-not-exist
- - - - - - - - - - - - - - - - - - - - - - - - -
Cek status db
$ pg_ctl status -D "..\data" (enter)
Stop database
$ pg_ctl stop -D "..\data" (enter)
Run/Start database
$ pg_ctl start -D "..\data" (enter)
- - - - - - - - - - - - - - - - - - - - - - - - -
Connect to PostgreSQL using cmd (general)
$ psql --host=127.0.0.1 --port=5432 --username=postgres --password --dbname=db_example
Connect to PostgreSQL using cmd (by pass access)
$ psql --host=127.0.0.1 --port=5432 --username=postgres
Change Password PostgreSQL
$ ALTER USER postgres WITH PASSWORD 'Jakarta098';
Restart service PotsgreSQL on windows.
Open cmd run as administrator, type command bellow :
$ SC STOP PostgreSQL-v9-p5433
$ SC START PostgreSQL-v9-p5433
- - - - - - - - - - - - - - - - - - - - - - - - -
Backup Database PostgreSQL to .sql File
$ psql --verbose --host=127.0.0.1 --port=5432 --username=postgres --password --dbname=db_example --format=p --encoding=UTF-8 --inserts --no-privileges --no-owner --file D:\database\postgresql\db_example.sql -n "public" db_example
Restore .sql File to Database PostgreSQL
$ psql --host=127.0.0.1 --port=5432 --username=postgres --password --dbname=db_example < D:\database\postgresql\db_example.sql
Restore .csv File into Table in PostgreSQL
$ psql --host=127.0.0.1 --port=5432 --username=postgres --password --dbname=DBNAME -c "COPY \"SCHEMA_NAME\".\"TABLE_NAME\" FROM 'C:\Users\admin\Documents\backup-db2-csv\TABLE_NAME.csv' WITH (FORMAT csv, HEADER true, DELIMITER ';')"
- - - - - - - - - - - - - - - - - - - - - - - - -
Boost Performance for PostgreSQL
Edit postgresql.conf
# DB Version: 10
# OS Type: windows
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 24
# Connections num: 200
# Data Storage: ssd
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2047MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 37449kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 24
max_parallel_workers_per_gather = 4
max_parallel_workers = 24
- - - - - - - - - - - - - - - - - - - - - - - - -
Source :
https://stackoverflow.com/questions/19674456/run-postgresql-queries-from-the-command-line
https://stackoverflow.com/questions/40632228/input-file-appears-to-be-a-text-format-dump-please-use-psql
https://git-maspaad.blogspot.com/2024/11/tutorial-install-postgresql-on-linux.html
https://stackoverflow.com/questions/14484652/register-and-run-postgresql-9-0-as-windows-service
https://pgtune.leopard.in.ua/?dbVersion=10&osType=windows&dbType=web&cpuNum=24&totalMemory=32&totalMemoryUnit=GB&connectionNum=200&hdType=ssd
https://gemini.google.com/share/41ae780c6b8c
Comments
Post a Comment