Showing posts with label Postgres. Show all posts
Showing posts with label Postgres. Show all posts
Thursday, June 19, 2025

Postgres: List all RANGE partitions, sorted by their range start value

›
My table contains 180 partitons, and looks like this when described in psql: mydb=> \d mytable Partitioned table "myschema.m...
Thursday, June 5, 2025

PostgreSQL search_path basics

›
Similar to Oracles alter session set current_schema=<schema_name> , in PostgreSQL, search_path is a session-level setting that dete...

How to limit connections in a postgres database

›
This is how you can restrict connections for a specific database to zero: postgres=# alter database db01 connection limit 0; ALTER DATABA...

create user in postgres - basic syntax

›
I will create a group role called common_users a user called ro_user1 in a database called db01 The read-only user ro_user1 should ...
Tuesday, June 3, 2025

PostgreSQL Memory Parameters and how they relate

›
Here are some of the important memory parameters in a postgreSQL server, and how they relate to one another. shared_buffers The amount o...

How to setup huge pages on a postgreSQL server

›
The Postgres Documentation states: The use of huge pages results in smaller page tables and less CPU time spent on memory management, inc...
Friday, May 23, 2025

How to find constraints on a table in postgres

›
SELECT con.conname AS constraint_name, CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u...
Thursday, May 22, 2025

PostgreSQL: difference between \copy and COPY

›
If you put a statement like this in a file: COPY at_locations FROM '/external/data/geolocations_at_fixed.csv' WITH ( FORMAT csv...
Wednesday, April 9, 2025

How to mimic sqlplus variable behaviour in PostgreSQL

›
Here is how you can mimic the usage of variables at the prompt using psql and PostgreSQL like you would with oracle and sqlplus/sqlc: \conn...
Wednesday, April 2, 2025

How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table

›
Create the table: CREATE TABLE mytable ( id bigserial, key text COLLATE pg_catalog."default" NOT NULL, outdata jsonb...
Monday, January 27, 2025

How to install and configure PG Bouncer on Linux x86_64

›
Install the PGBouncer software List packages allready installed on the system: yum list installed |grep bounce Search repository: yum sea...
Friday, December 27, 2024

How to check why a PostgreSQL server won't start

›
journalctl -u postgresql-15.service Example output: Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service...
Friday, July 19, 2024

How to check if your PostgreSQL server is part of a replication setup

›
If the server is down, check the entries in postgresql.auto.conf. The following postgres.auto.conf is from a slave server: wal_level = ...
Wednesday, March 6, 2024

How to remove all postgres packages on a Linux RH server

›
Logged in as root on the server you would like to remove all postgres packages from: Stop and disable the current postgres services (in th...
Thursday, September 14, 2023

How to install an extension in a postgreSQL database

›
Are the desired extensions available in our installation? [postgres@myserver.no ~]$ echo "SELECT * FROM pg_available_extensions;...
Friday, December 2, 2022

How to alter a column to use a sequence generated number

›
In my example, mytable contains a columnn called id, which is of datatype bigint. If you want to use a sequence to automatically popluate...
Friday, October 21, 2022

Rename schema in PostgreSQL: syntax

›
\connect db01 alter schema schema1 rename to schema1_old; Documented here

Rename database in PostgreSQL: syntax

›
alter database db01 rename to db01; Documented here See this post for how to create a new database in PostgreSQL
Wednesday, October 19, 2022

How to prevent a user from login into a postgres database

›
alter role scott with nologin; ALTER ROLE Remember that in postgres, "users" and "roles" are used interchangably, so...
Wednesday, August 31, 2022

Rename table in postgreSQL: syntax

›
Rename a table: alter table myschema.mytable rename to mytable_old; Change ownership for a table: alter table myschema.mytable owner to ...