all about postgres

postgres.md

Connect

psql -U postgres

Create Db

CREATE DATABASE my_test_db

List Db

\l

Use Db

\c my_test_db

List Tables

\d

Show Tables

\c database_name;

Alter Table

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

Connect DB

psql -U postgres -d northwind

HAProxy

backend be_servers
  server server1 127.0.0.1:8080 check weight 100
  server server2 127.0.0.1:8081 check weight 100

https://www.haproxy.com/documentation/haproxy-runtime-api/reference/set-weight/
http://www.haproxy.org/download/1.4/doc/configuration.txt

Scale
https://www.sql-easy.com/learn/how-to-scale-postgresql/

https://www.timescale.com/learn/guide-to-postgresql-scaling

Backup mysql db
https://linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/

Constraints at DB Level

https://www.scaler.com/topics/postgresql/table-level-constraints-in-postgresql/
https://www.prisma.io/dataguide/postgresql/column-and-table-constraints

Get comma separated data

You can use the array() and array_to_string() functions together with your query.
With SELECT array( SELECT id FROM table ); you will get a result like: {1,2,3,4,5,6}

Then, if you wish to remove the {} signs, you can just use the array_to_string() function and use comma as separator, so: SELECT array_to_string( array( SELECT id FROM table ), ',' ) will get a result like: 1,2,3,4,5,6

https://www.dataquest.io/blog/install-postgresql-14-7-for-macos/

pg_hba.conf path

psql -U postgres -c 'show hba_file'

Restart on mac

brew services restart postgresql@16