PostgreSQL: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| Line 822: | Line 822: | ||
|- | |- | ||
|valign='top' style='width:50%'| | |valign='top' style='width:50%'| | ||
* [[PostgreSQL/Large Object|Skipped » Find More 👉 PostgreSQL » Large Object]] | |||
|valign='top' style='width:50%'| | |valign='top' style='width:50%'| | ||
|} | |} | ||
Revision as of 06:58, 16 January 2026
Login to PSQL
|
Login to PSQL | |
|---|---|
export PGPASSWORD='p@55w0rd'
# deploy@cdn.chorke.org:~ #
psql -U postgres
#psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
#Type "help" for help.
# root@cdn.chorke.org:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
psql
# psql (9.6.8)
# Type "help" for help.
#
# postgres=#
|
|
Connection String
|
Connection String | |
|---|---|
# parameters based psql
psql -U postgres
psql -U academia postgres
psql -U academia -h rds.vpc.chorke.org postgres
psql -U academia -h rds.vpc.chorke.org -p 5432 postgres
PGPASSWORD='sadaqah' psql -U academia -h rds.vpc.chorke.org -p 5432 postgres
|
# connection string based psql
psql 'postgres://postgres:@:/'
psql 'postgres://academia:@:/postgres'
psql 'postgres://academia:@rds.vpc.chorke.org:/postgres'
psql 'postgres://academia:@rds.vpc.chorke.org:5432/postgres'
psql 'postgres://academia:sadaqah@rds.vpc.chorke.org:5432/postgres'
|
Login Error
|
Connection String | |
|---|---|
# root@cdn.chorke.org:~ #
su - postgres psql
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# /bin/createuser: /bin/createuser: cannot execute binary file
|
# root@cdn.chorke.org:~ #
su postgres
psql -p 5433
# psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
|
# deploy@cdn.chorke.org:~ #
sudo su postgres
psql -p 5433
# psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
|
|
Cluster Login
|
Cluster Login | |
|---|---|
pg_lsclusters
sudo su - postgres
|
psql -p 5433
psql -U postgres -p 5433
|
GRANT ALL ON tmp_coll TO academia;
GRANT ALL ON test_json TO academia;
|
-- psql -V
SELECT version();
|
Creating User
|
Creating User | |
|---|---|
# root@cdn.chorke.org:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createuser root
# createuser: creation of new role failed: ERROR: role "root" already exists
# ALTER USER root WITH SUPERUSER;
|
|
Creating DB
|
Creating DB | |
|---|---|
|
Until creating root database it's always show error |
|
# root@cdn.chorke.org:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createdb root
|
|
|
Similarly you can create a database as same as your frequently used OS User. In this case no need password to login. It will authenticated from OS. That's means you can login to your PostgreSQL user using OS Authentication. |
|
Grant Privileges
|
Grant Privileges | |
|---|---|
# postgres=#
grant all privileges on database root to root;
# GRANT
# postgres=#
grant all privileges on database postgres to root;
# GRANT
|
|
Extensions
|
Extensions | |
|---|---|
ENABLE_DB_LINK=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS dblink SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'dblink';
DDL
)
echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER}
echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER} academia_data_staging
|
ENABLE_PG_CRYPTO=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'pgcrypto';
DDL
)
echo "${ENABLE_PG_CRYPTO}" | psql -p 5432 -U ${USER}
echo "${ENABLE_PG_CRYPTO}" | psql -p 5432 -U ${USER} academia_data_staging
|
sudo apt install postgresql-16-pgvector
ENABLE_PG_VECTOR=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vector SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'vector';
DDL
)
echo "${ENABLE_PG_VECTOR}" | psql -p 5432 -U ${USER}
echo "${ENABLE_PG_VECTOR}" | psql -p 5432 -U ${USER} academia_data_staging
|
|
Logout PSQL
|
Logout PSQL | |
|---|---|
# postgres=#
\q
# -bash-4.2$
|
|
Meta Data
|
Meta Data | |
|---|---|
-- show databases
SELECT datname FROM pg_database; -- \l+
-- show sequence
SELECT c.relname FROM pg_class c -- \ds+
WHERE c.relkind = 'S' ORDER BY c.relname;
-- show tables
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema');
-- describe tables
SELECT table_name, column_name, data_type -- \d+ my_table
FROM information_schema.columns WHERE table_name = 'my_table';
-- find tables with similar field name
SELECT table_name, column_name, data_type
FROM information_schema.columns WHERE column_name LIKE '%slug%';
|
SELECT pg_database_size('my_database'); -- \l+
SELECT pg_size_pretty(pg_database_size('my_database')); -- \l+
SELECT
db.datname AS db_name,
pg_size_pretty(pg_database_size(db.datname)) AS db_size
FROM pg_database db ORDER BY pg_database_size(db.datname) DESC;
SELECT pg_size_pretty(pg_total_relation_size('my_table')); -- \dt+
SELECT -- \dt+
table_schema, table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS table_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY 2 ASC;
|
XML Query
|
XML Query | |
|---|---|
Array Query
|
XML Query | |
|---|---|
SELECT dependencies||'"Running Fiscal Year"'
FROM scope WHERE name = 'Fiscal Year Config';
|
|
SELECT * FROM user
WHERE owner_ref LIKE ANY (ARRAY[
',academia,chorke.org,shahed.biz,'
]) ORDER BY id ASC;
|
SELECT * FROM user
WHERE owner_ref LIKE ALL (ARRAY[
',academia,chorke.org,shahed.biz,'
]) ORDER BY id ASC;
|
JSON Query
|
JSON Query | |
|---|---|
Delete Join
|
Delete Join |
|---|
-- USING is not an ANSI standard not supported by others RDMBS
-- it's better to use sub query rather than USING for join
DELETE FROM product p USING category c
WHERE p.category_id = c.id AND
c.name = 'Grocery';
|
Sequence
|
Sequence |
|---|
CREATE SEQUENCE IF NOT EXISTS public.policy_1000
INCREMENT 1
START 46656 -- 1,000
MINVALUE 46656 -- 1,000
MAXVALUE 1679615 -- Z,ZZZ
CACHE 10;
ALTER SEQUENCE public.policy_1000 OWNER TO chorke_init_pro;
COMMENT ON SEQUENCE public.policy_1000 IS 'Academia Policy Sequence';
SELECT CURRVAL('policy_1000');
SELECT SETVAL('policy_1000', 0);
ALTER SEQUENCE IF EXISTS policy_1000 RESTART 1;
SELECT NEXTVAL('policy_1000')::int AS policy_id;
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO chorke_init_pro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO chorke_init_pro;
|
Echo/Print
|
Echo/Print |
|---|
DO $BODY$
DECLARE
p_jsonb jsonb;
BEGIN
-- SELECT INTO p_jsonb '["", "template","{templateType}","{productSlug}"]'::jsonb || '["{filePath}"]'::jsonb;
p_jsonb := '["", "template","{templateType}","{productSlug}"]'::jsonb || '["{filePath}"]'::jsonb;
RAISE NOTICE '%', p_jsonb;
END;
$BODY$;
|
Execute DDL
Insert Using Loop
|
Insert Using Loop |
|---|
DO $BODY$
DECLARE
p_id integer;
p_domain varchar:= 'chorke.org';
p_group_name varchar:= 'occupation_class';
BEGIN
SELECT INTO p_id (COALESCE(MAX(id), 0)) FROM grouped_collection;
FOR p_code IN 1..5 LOOP
INSERT INTO grouped_collection (id, created_on, domain, code, name, group_name, extended_properties)
VALUES ((p_id + p_code), LOCALTIMESTAMP, p_domain, p_code, p_code, p_group_name, '{}');
END LOOP;
END;
$BODY$;
|
Anonymous Blocks
|
Anonymous Blocks |
|---|
DO
$BODY$
DECLARE
fiscal_year_ukc character varying;
fiscal_drop_ukc character varying := 'ALTER TABLE fiscal_year';
BEGIN
SELECT
INTO fiscal_year_ukc con.conname
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE
nsp.nspname = 'public' AND
rel.relname = 'fiscal_year' AND
con.conname LIKE 'uk_%';
IF fiscal_year_ukc IS NOT NULL THEN
fiscal_drop_ukc := fiscal_drop_ukc || ' '
'DROP CONSTRAINT ' || fiscal_year_ukc;
EXECUTE fiscal_drop_ukc;
END IF;
END
$BODY$;
|
Create Sequence
|
Create Sequence |
|---|
CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying)
RETURNS integer
LANGUAGE 'plpgsql'
AS
$BODY$
DECLARE
policy_no integer;
sequence_name character varying := 'policy_' || agent_no;
create_sequence character varying := 'CREATE SEQUENCE IF NOT EXISTS ' || sequence_name;
BEGIN
create_sequence := create_sequence || ' '
'INCREMENT 1 '
'START 46656 ' -- 1,000
'MINVALUE 46656 ' -- 1,000
'MAXVALUE 1679615 ' -- Z,ZZZ
'CACHE 10 ';
EXECUTE create_sequence;
SELECT INTO policy_no NEXTVAL(sequence_name)::int;
RETURN policy_no;
END
$BODY$;
SELECT fn_get_policy_no('0001');
|
Create Sequence with Comment
|
Create Sequence with Comment |
|---|
CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying, IN agent_desc character varying)
RETURNS integer
LANGUAGE 'plpgsql'
AS
$BODY$
DECLARE
policy_no integer;
sequence_name character varying := 'policy_' || agent_no;
create_sequence character varying := 'CREATE SEQUENCE IF NOT EXISTS ' || sequence_name;
comment_sequence character varying := 'COMMENT ON SEQUENCE ' || sequence_name ||' IS ''' || agent_desc || '''';
BEGIN
create_sequence := create_sequence || ' '
'INCREMENT 1 '
'START 46656 ' -- 1,000
'MINVALUE 46656 ' -- 1,000
'MAXVALUE 1679615 ' -- Z,ZZZ
'CACHE 10 ';
EXECUTE create_sequence;
EXECUTE comment_sequence;
SELECT INTO policy_no NEXTVAL(sequence_name)::int;
RETURN policy_no;
END
$BODY$;
SELECT fn_get_policy_no('0001', 'Fareast Islami Life Insurance');
-- @Query(value="SELECT fn_get_policy_no(:agent_no, :agent_desc)", nativeQuery = true)
-- Integer getPolicyNo(@Param("agent_no") String agentNo, @Param("agent_desc") String agentDesc);
|
Create From Table Exclude Column
|
Create Sequence with Comment |
|---|
DO
$BODY$
DECLARE
create_table character varying;
BEGIN
SELECT INTO create_table 'CREATE TABLE IF NOT EXISTS invoice__temp AS SELECT ' || STRING_AGG(isc.column_name, ', ') || ' FROM invoice'
FROM (SELECT * FROM information_schema.columns WHERE table_name = 'invoice' AND
column_name NOT IN ('id', 'created_on') ORDER BY ordinal_position ASC) isc;
EXECUTE create_table;
END
$BODY$;
|
Date & Time
|
Date & Time | |
|---|---|
SELECT * FROM policy
WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');
UPDATE policy SET deleted_on = LOCALTIMESTAMP
WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');
|
|
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_TRUNC('year', TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_TRUNC('hour', TIMESTAMP '2013-10-10 10:10:10');
SELECT AGE(TIMESTAMP '2023-10-10', TIMESTAMP '1983-10-10');
SELECT EXTRACT(DAY FROM TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_PART('day', TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_PART('hour', INTERVAL '4 hours 3 minutes');
SELECT JUSTIFY_INTERVAL(INTERVAL '1 mon -1 hour');
SELECT JUSTIFY_HOURS(INTERVAL '27 hours');
SELECT JUSTIFY_DAYS(INTERVAL '35 days');
SELECT AGE(TIMESTAMP '1983-10-10');
SELECT CURRENT_TIMESTAMP(2)
SELECT CURRENT_TIMESTAMP;
SELECT LOCALTIMESTAMP;
SELECT CURRENT_TIME;
SELECT CURRENT_DATE;
|
|
String Format
|
String Format | |
|---|---|
SELECT STRING_TO_ARRAY('/my/request/path', '/') "string_array";
SELECT ARRAY_TO_JSON(STRING_TO_ARRAY('/my/request/path', '/')) "json_array";
|
|
Number Format
|
Number Format | |
|---|---|
SELECT NULLIF(regexp_replace('CKI00109' , '\D','','g'), '')::numeric;
SELECT NULLIF(regexp_replace('CKI0010#9####', '\D','','g'), '')::numeric;
SELECT NULLIF(regexp_replace('CKI2203000075', '\D','','g'), '')::numeric;
|
|
Restoring the Dump
|
Restoring the Dump | |
|---|---|
# export dumps
pg_dump academia > academia_20200210_1020.sql
pg_dump academia| gzip > academia_2020021.sql.gz
|
|
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname IN
(
'academia'
);
DROP DATABASE IF EXISTS academia;
CREATE DATABASE academia;
|
|
# restore dumps
psql academia < academia_20200210_1020.sql
gunzip -c academia_20200210_1020.sql.gz | psql academia
# export table as csv
psql -U postgres
\c my_database_staging
\COPY my_table_name TO '/home/academia/Downloads/my_table_name.csv' DELIMITER ',' CSV HEADER;
|
|
OS User Auth
|
OS User Auth | |
|---|---|
CREATE DATABASE academia;
CREATE USER academia WITH LOGIN;
GRANT ALL PRIVILEGES ON DATABASE academia TO academia;
-- CREATE USER academia WITH ENCRYPTED PASSWORD 'sadaqah!';
|
|
echo "os-map academia academia" >> $PG_DATA/pg_ident.conf
echo "host all all 127.0.0.1/32 ident map=os-map" >> $PG_DATA/pg_hba.conf
pg_ctl --pgdata="$PG_DATA" reload;
|
|
DBLink POC
|
DBLink POC | |
|---|---|
DBLink Exec
|
DBLink Exec | |
|---|---|
Recursive Query
|
Recursive Query | |
|---|---|
WITH RECURSIVE hierarchy AS (
SELECT
o.id,
o.code, o.name,
o.parent "parent_code",
p.name "parent_name"
FROM org_unit o
LEFT JOIN org_unit p ON p.code = o.parent
WHERE o.code = LOWER(REPLACE('Chorke Agency', ' ', '-'))
UNION
SELECT
o.id,
o.code, o.name,
o.parent "parent_code",
p.name "parent_name"
FROM org_unit o
LEFT JOIN org_unit p ON p.code = o.parent
INNER JOIN hierarchy h ON h.parent_code = o.code
)
SELECT * FROM hierarchy;
|
|
Encode/Decode
|
Encode/Decode | |
|---|---|
SELECT decode('Chorke Academia, Inc.', 'escape'); -- text to binary
SELECT encode('Chorke Academia, Inc.'::bytea, 'escape'); -- binary to text
SELECT convert_to('Chorke Academia, Inc.', 'UTF8'); -- text to binary
SELECT convert_from('Chorke Academia, Inc.'::bytea, 'UTF8'); -- binary to text
SELECT encode('Chorke Academia, Inc.'::bytea, 'base64'); -- text to base64
SELECT convert_from(decode('Q2hvcmtlIEFjYWRlbWlhLCBJbmMu', 'base64'), 'UTF8'); -- base64 to text
| |
| Base36 Encode | |
CREATE OR REPLACE FUNCTION fn_base36_encode(IN base10 bigint)
RETURNS varchar
LANGUAGE plpgsql IMMUTABLE
AS
$BODY$
DECLARE
base36 varchar := '';
intval bigint := abs(base10);
char0z char[] := regexp_split_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '');
BEGIN
WHILE intval != 0 LOOP
base36 := char0z[(intval % 36)+1] || base36;
intval := intval / 36;
END LOOP;
IF base10 = 0 THEN base36 := '0'; END IF;
RETURN base36;
END
$BODY$;
| |
| Base36 Decode | |
CREATE OR REPLACE FUNCTION fn_base36_decode(IN base36 varchar)
RETURNS bigint
LANGUAGE plpgsql
AS
$BODY$
DECLARE
rindex int;
intval int;
carray char[];
base10 bigint := 0;
char0z varchar := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
FOR rindex IN REVERSE char_length(base36)..1 LOOP
carray := carray || substring(upper(base36) FROM rindex FOR 1)::char;
END LOOP;
rindex := 0;
WHILE rindex < (array_length(carray,1)) LOOP
intval := position(carray[rindex+1] IN char0z)-1;
base10 := base10 + (intval * (36 ^ rindex));
rindex := rindex + 1;
END LOOP;
RETURN base10;
END
$BODY$;
|
|
Large Object
|
Large Object | |
|---|---|
Bastion Dump
|
Bastion Dump | |
|---|---|
BASH_REMOTE_SCRIPT_FRMT=$(cat <<'EOF'
# suppress stdout
echo '/* $STDOUT$'
echo "${HOSTNAME}${HOME}"
ssh -qt gtw.vpc.chorke.org <<'EOF_00'
echo "${HOSTNAME}${HOME}"
ssh -qt app.vpc.chorke.org <<'EOF_01'
echo "${HOSTNAME}${HOME}"
echo '$STDOUT$ */'
pg_dump postgres://academia:%s@rds.vpc.chorke.org:5432/%s
# suppress stdout
echo '/* $STDOUT$'
EOF_01
EOF_00
echo '$STDOUT$ */'
EOF
)
|
|
BASH_REMOTE_SCRIPT_PASS='sadaqah!'
BASH_REMOTE_SCRIPT_PGDB='academia_keycloak_staging'
printf -v BASH_REMOTE_SCRIPT_EXEC "${BASH_REMOTE_SCRIPT_FRMT}" "${BASH_REMOTE_SCRIPT_PASS}" "${BASH_REMOTE_SCRIPT_PGDB}"
printf -v BASH_EXPORT_SCRIPT_DUMP '%s.sql' "${BASH_REMOTE_SCRIPT_PGDB}"
bash -c "${BASH_REMOTE_SCRIPT_EXEC}" > ${BASH_EXPORT_SCRIPT_DUMP}
| |
sed '/^\/\* \$STDOUT\$$/,/^\$STDOUT\$ \*\/$/{/^\/\* \$STDOUT\$$/!{/^\$STDOUT\$ \*\/$/!d}}' -i ${BASH_EXPORT_SCRIPT_DUMP}
sed -z 's|\n/\* $STDOUT$\n$STDOUT$ \*/||g' -i ${BASH_EXPORT_SCRIPT_DUMP}
sed -z 's|/\* $STDOUT$\n$STDOUT$ \*/\n||g' -i ${BASH_EXPORT_SCRIPT_DUMP}
gzip ${BASH_EXPORT_SCRIPT_DUMP}
| |
Export CSV
|
Export CSV |
|---|
psql -U academia academia_audit_staging
\COPY (SELECT * FROM audit_log WHERE action_type = 'Sync Collection' AND DATE_TRUNC('day', logged_on) = '20241010')\
To '~/.config/audit_log_20241010_1010_MYT.csv' With CSV DELIMITER ',' HEADER;
|
Knowledge
|
Knowledge | |
|---|---|
# wsl (windows subsystem for linux)
sudo service redis-server restart
sudo service postgresql restart
sudo service apache2 restart
# comment and uncomment sql
sed -i -e 's|^|-- |g' src/main/resources/db/migration/*.sql
sed -i -e 's|^-- ||g' src/main/resources/db/migration/*.sql
|
|
-- psql
CREATE DATABASE chorke_init_pro;
CREATE USER chorke_init_pro WITH ENCRYPTED PASSWORD 'pa55w0rd';
GRANT ALL PRIVILEGES ON DATABASE chorke_init_pro TO chorke_init_pro;
-- \q
|
-- revoke public connect from specific database after creation
REVOKE CONNECT ON DATABASE academia_ebis_dev FROM PUBLIC;
-- revoke public connect for all future databases
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
|
SELECT
con.conname,
nsp.nspname,
rel.relname
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE
nsp.nspname = 'public' AND
rel.relname = 'fiscal_year' AND
con.conname LIKE 'uk_%';
|
|
SELECT
n.nspname AS "schema",
c.relname AS "name",
CASE c.relkind
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'r' THEN 'table'
WHEN 's' THEN 'special'
WHEN 'S' THEN 'sequence'
WHEN 'f' THEN 'foreign table'
WHEN 'p' THEN 'partitioned table'
WHEN 'I' THEN 'partitioned index'
WHEN 'm' THEN 'materialized view'
END AS "type",
pg_catalog.pg_get_userbyid(c.relowner) AS "owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
|
|
SELECT d.datname AS "db_name",
pg_catalog.pg_get_userbyid(d.datdba) AS "db_owner",
pg_catalog.pg_encoding_to_char(d.encoding) AS "db_encoding",
d.datcollate AS "db_collation",
d.datctype AS "db_collation_type",
pg_catalog.array_to_string(d.datacl, e'\n') AS "db_access_privileges",
CASE
WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN
pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS "db_size",
t.spcname AS "db_tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') AS "db_description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid
ORDER BY 1;
|
|
SELECT
usesysid AS "user_id",
usename AS "user_name",
passwd AS "user_password",
usesuper AS "is_super_user"
FROM pg_catalog.pg_user ORDER BY 1;
|
|
DO
$BODY$
DECLARE
p_password varchar :='p@$$w0rd';
p_connection_string varchar :='hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=';
BEGIN
p_connection_string := p_connection_string || p_password;
RAISE NOTICE 'Connection String: %', p_connection_string;
SELECT INTO p_connection_string
'hostaddr=' || host(inet_server_addr()) || ' port=' || inet_server_port() ||
' dbname=' || current_database() || ' user=' || CURRENT_USER || ' password=' || p_password;
RAISE NOTICE 'Connection String: %', p_connection_string;
END
$BODY$;
|
|
cat << EOF | psql -U postgres
DROP DATABASE IF EXISTS academia_ebis_staging;
CREATE DATABASE academia_ebis_staging;
CREATE USER academia WITH ENCRYPTED PASSWORD 'sadaqah!';
GRANT ALL PRIVILEGES ON DATABASE academia_ebis_staging TO academia;
ALTER DATABASE academia_ebis_staging OWNER TO academia;
-- ALTER USER academia WITH NOSUPERUSER;
-- ALTER USER academia WITH SUPERUSER;
EOF
|
|
Allow Remote
|
Docker » Compose » Manage | |
|---|---|
apt install -y iputils-ping telnet dnsutils
sudo systemctl status ufw
sudo ufw status verbose
|
sudo ufw status numbered
sudo ufw allow 5432/tcp
sudo ufw enable
|
nano /etc/postgresql/14/main/postgresql.conf
nano /etc/postgresql/16/main/postgresql.conf
|
nano /etc/postgresql/14/main/pg_hba.conf
nano /etc/postgresql/16/main/pg_hba.conf
|
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
|
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
# host all all 10.19.83.1/24 scram-sha-256
# host all all 10.20.13.1/24 scram-sha-256
|
Spread Sheet
|
Spread Sheet |
|---|
=CONCAT("(LOCALTIMESTAMP, 'religion', 'chorke.org', (SELECT (COALESCE(MAX(id), 0) + 1) FROM grouped_collection), '", SUBSTITUTE(A2 , """" , ""), "', '", SUBSTITUTE(A2 , """" , ""), "', '{""state"": """ , SUBSTITUTE(B2 , "'" , "''") , """, ""city"": """ , SUBSTITUTE(C2 , "'" , "''") , """}'),")
=CONCAT("[""", SUBSTITUTE(A2 , """", ""), """, """, SUBSTITUTE(A2, """", ""), "",""", {""state"": """ , SUBSTITUTE(B2 , "'" , "''") , """, ""city"": """ , SUBSTITUTE(C2 , "'" , "''") , """}", "],")
=CONCAT("[""", SUBSTITUTE(C518 , """", ""), """, """, SUBSTITUTE(B518, """", ""), "",""", {}, " , A518 , ", ", IF(ISBLANK(D518), "false", "true"), "],")
=CONCAT("[""", SUBSTITUTE(C5 , """", ""), """, """, SUBSTITUTE(B5, """", ""), "",""", {""class"": """, D5 ,""", ""income"": true}, " , A5, "],")
=CONCAT("[""", SUBSTITUTE(C27 , """", ""), """, """, SUBSTITUTE(B27, """", ""), "",""", {}, " , A27, "],")
=CONCAT("[""", C27, """, """, B27, "",""", {}, " , A27, "],")
|
Docker PSQL
|
Docker PSQL | ||
|---|---|---|
docker run -d --name postgres\
-e POSTGRES_PASSWORD=sadaqah\
-v ./init.sql:/docker-entrypoint-initdb.d/init.sql\
-p 127.0.0.1:5433:5432\
postgres:12.14
|
docker exec -it postgres bash
psql -U postgres
docker stop postgres
docker rm postgres
|
docker exec -it postgres\
psql -U postgres
docker ps -a
docker rm postgres -f
|