allow connection client remote postgresql on remote
danang setya pramanaedit pg_hba.conf and add host all all 192.168.1.0/24 scram-sha-256 after that enter on…
Backup & Restore Postgre SQL Database
pg_dump and pg_restore are command-line utilities for backing up and restoring PostgreSQL databases. The tar format is one of the non-plain text archive formats that pg_dump can produce and which pg_restore is designed to handle. Backing Up with pg_dump (TAR format) …
change all tables owner to specified user
first use select to show all tables from old owner SELECT tablename, schemaname,tableowner FROM pg_tables WHERE schemaname = 'public' AND tableowner = 'oldowner'; and make script for looping alter table…
To drop all primary key constraints in the public schema of a PostgreSQL database
DO $$ DECLARE r RECORD; BEGIN -- Select all primary key constraints in the 'public' schema FOR r IN ( SELECT table_name, constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY'…
strict change column type table postgre from string to int
ALTER TABLE table_name ALTER COLUMN column_name TYPE BIGINT USING (column_name::bigint);
drizzle init for postgres nextjs
sebelumnya buat .env dan isi connection string untuk postgre POSTGRE_URL="postgresql://postgres:userpassword@localhost:5432/dbname" buat drizzle config import { defineConfig } from "drizzle-kit"; export default defineConfig({ schema: "./src/lib/db/postgresql/schema.ts", out: "./src/lib/drizzle/postgresql", dialect: "postgresql", dbCredentials: {…
migrate database from mysql to postgre using docker pg_loader
karena saya menggunakan jendela saat ini , terpaksa saya menggunakan pg_loader dari docker , pertama ambil packagenya dulu dari remote milik dimitri docker pull ghcr.io/dimitri/pgloader:latest pertama pastikan kalian punya akses…
postgre query grant & alter
mengubah privileges user untuk semua tables GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user; ubah nama field di dalam table ALTER nama_table RENAME COLUMN nama_lama TO nama_baru;…
grant new user to database postgre
GRANT CONNECT ON DATABASE mydatabase TO myuser; GRANT USAGE ON SCHEMA public TO myuser; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
postgre SQL Connection String
have 2 version of it postgres://username:password@127.0.0.1:5432/dbname AND postgresql://username:password@127.0.0.1:5432/dbname