Fix Sequence Postgres

 SELECT
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname;

 

 

Ref: https://wiki.postgresql.org/wiki/Fixing_Sequences

 

Fix lc_* message error when start postgres

 localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8

Create readonly user in Postgres

CREATE USER testuser WITH PASSWORD 'somepassword';
GRANT CONNECT ON DATABASE dbtest TO testuser;

//remove all permission, only superuser can perform db actions
REVOKE ALL ON schema public FROM public;
//remove all table permission from user
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM testuser;
//grant select permission to user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO testuser;

Build Citus from source

apt-get install -y libreadline-dev libselinux1-dev libxslt-dev libpam0g-dev git flex make libssl-dev libicu-dev libkrb5-dev libcurl4-gnutls-dev autoconf llvm-7 clang-7

set PG_CONFIG enviroment point to pgsql/bin/pg_config

git clone https://github.com/citusdata/citus.git
cd citus
./configure
make
make install

Add shared_preload_libraries to postgresql.conf

echo "shared_preload_libraries = 'citus'" >> postgres/databaase/postgresql.conf

psql template1
CREATE EXTENSION citus;

Create read only user on Postgres

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;

Change owner all database objects to new role

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" DBNAME` ; do  psql -c "alter table \"$tbl\" owner to NEWUSER" DBNAME ; done

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" DBNAME` ; do  psql -c "alter table \"$tbl\" owner to NEWUSER" DBNAME ; done

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" DBNAME` ; do  psql -c "alter table \"$tbl\" owner to NEWUSER" DBNAME ; done

Encrypt data postgres

CREATE EXTENSION pgcrypto;

 

SELECT ENCODE(DIGEST('sometext','sha256'),'hex');

 

Portable Postgres

Postgres full extension 9.6.3 on Debian 8 64bit 
https://drive.google.com/open?id=0B30EeVPAcvhvWHpMNDZmbFdNYVk

Function slower than query direct Postgres

For queries on tables with even data distribution, this will generally be no problem and PL/pgSQL functions will perform somewhat faster than raw SQL queries or SQL functions. But if your query can use certain indexes depending on the actual values in the WHERE clause or, more generally, chose a better query plan for the particular values, you may end up with a sub-optimal query plan.
(source: http://stackoverflow.com/questions/9305133/why-does-postgresql-treat-my-query-differently-in-a-function)

RETURN QUERY : cache plan
RETURN QUERY EXECUTE: replan for each call

RETURN QUERY EXECUTE $$
//query body
$$ USING param1, param2

Add column to all tables same schema

CREATE OR REPLACE FUNCTION add_version_column_to_all_tables()
RETURNS VOID
AS $$
DECLARE
 my_row RECORD;
BEGIN
 FOR my_row IN
 SELECT *
 FROM information_schema.tables
 WHERE table_schema = 'public' AND table_type='BASE TABLE'
 LOOP
 IF NOT EXISTS
 (
 SELECT attname FROM pg_attribute WHERE attrelid =
 (SELECT oid FROM pg_class WHERE relname = my_row.table_name )
 AND attname = 'subtenant'
 )
 THEN
     IF EXISTS
     (
         SELECT attname FROM pg_attribute WHERE attrelid =
         (SELECT oid FROM pg_class WHERE relname = my_row.table_name )
         AND attname = 'tenantid'
     )
     THEN
         EXECUTE('ALTER TABLE ' || my_row.table_name || ' ADD COLUMN subtenant text;');
         EXECUTE('UPDATE ' || my_row.table_name || ' SET subtenant = tenantid;');
     END IF;
 END IF;
 END LOOP;
END
$$
LANGUAGE plpgsql;

SELECT add_version_column_to_all_tables();

Build Postgres from source

Install libs for compile source postgres

apt-get install build-essential libreadline-dev zlib1g-dev llvm-7 clang-7

if use Debian 11 or Ubuntu 22.04, change llvm and clang to current version

apt-get install build-essential libreadline-dev zlib1g-dev llvm clang libicu-dev pkg-config bison flex libssl-dev
 

Extract postgres source to postgres-source-dir (change lllvm-7 to current version llvm path)

mkdir -p /home/userpath/postgres/pgsql
cd postgres-source-dir

./configure --with-llvm LLVM_CONFIG="/usr/lib/llvm-7/bin/llvm-config" --prefix=/home/userpath/postgres/pgsql

make

make install

if you want to install postgres-contrib extension, you can compile it

cd contrib
make
make install

cd \

mkdir postgres

cd postgres

mkdir database

pgsql/bin/initdb --locale en_US.UTF-8 -D database

Now you can setup everything with new portable postgres include 2 folder: pgsql and database

Run portable postgres 9.5.3 on Debian 8 64bit

I built portable postgres 9.5.3 from source for Debian 8 64bit. You can download it and free to use wihout install any dependencies.

Get file from https://drive.google.com/open?id=0B30EeVPAcvhvZmNFbXFidFVYRTA

tar xvf postgresql_9.5.3_portable_debian8_64.tar.gz

cd postgres

sh start_postgres.sh

To stop postgres

sh stop_postgres.sh

To use other command of Postgres:

source config_postgres

then all commands of postgres should be work properly

Update link for Postgres 9.6.2: https://drive.google.com/open?id=0B30EeVPAcvhvRHotSDdxYTA2LTA