1
--prefix=PATH              path untuk semua file
2
--exec-prefix=PATH         path untuk file bergantung arsitektur
3
--bindir=PATH              path program executable
4
--sysconfdir=PATH          path file konfigurasi
5
--with-pgport=NUMBER       port server
6
--with-perl                dukungan Perl
7
--with-python              dukungan Python
8
--with-openssl             dukungan OpenSSL
9
--with-ldap                dukungan LDAP
10
--with-blocksize=BLOCKSIZE ukuran page dalam KB
11
--with-wal-segsize=SEGSIZE ukuran segmen WAL dalam MB

1
SELECT setting FROM pg_settings WHERE name='data_directory';

1
\q

1
SELECT INTEGER[];
2
SELECT INTEGER[3];
3
SELECT INTEGER[][];
4
SELECT INTEGER[3][3];
5
SELECT INTEGER ARRAY;
6
SELECT INTEGER ARRAY[3];

1
SELECT '{0,1,2}';
2
SELECT '{{0,1},{1,2}}';
3
SELECT ARRAY[0,1,2];
4
SELECT ARRAY[ARRAY[0,1], ARRAY[1,2]];

1
WITH arr AS (SELECT ARRAY[0,1,2] int_arr)
2
SELECT int_arr[1] FROM arr;

1
WITH arr AS (SELECT ARRAY[0,1,2] int_arr)
2
SELECT int_arr[1:2] FROM arr;

1
WITH arr AS (SELECT ARRAY[0,1,2] int_arr)
2
SELECT ARRAY_DIMS(int_arr) FROM arr;
3

4
WITH arr AS (SELECT ARRAY[0,1,2] int_arr)
5
SELECT ARRAY_LENGTH(int_arr, 1) FROM arr;
6

7
WITH arr AS (SELECT ARRAY[0,1,2] int_arr)
8
SELECT cardinality(int_arr) FROM arr;

1
SELECT (DATE_TRUNC('MONTH', ('201608' || '01')::DATE) + INTERVAL '1 MONTH - 1 day')::DATE;

1
SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'DD Mon YYYY HH:MI:SSPM');

1
12 Aug 2016 04:40:32PM

1
SELECT TO_CHAR(
2
  '2016-08-12 16:40:32'::TIMESTAMP,
3
  '"Today is "FMDay", the "DDth" day of the month of "FMMonth" of "YYYY'
4
);

1
SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'TMDay, DD" de "TMMonth" del año "YYYY');

1
SELECT DATE_TRUNC('week', <>) AS "Week", COUNT(*)
2
FROM <>
3
GROUP BY 1
4
ORDER BY 1;

1
\d tablename
2
\d+ tablename

1
\d

1
CREATE TABLE person (
2
    person_id BIGINT NOT NULL,
3
    last_name VARCHAR(255) NOT NULL,
4
    first_name VARCHAR(255),
5
    age INT NOT NULL,
6
    PRIMARY KEY (person_id)
7
);
8

9
CREATE TABLE people_over_30 AS
10
SELECT * FROM person WHERE age > 30;

1
CREATE UNLOGGED TABLE person (
2
    person_id BIGINT NOT NULL PRIMARY KEY,
3
    last_name VARCHAR(255) NOT NULL,
4
    first_name VARCHAR(255),
5
    address VARCHAR(255),
6
    city VARCHAR(255)
7
);

1
CREATE TABLE person (
2
    person_id BIGINT NOT NULL,
3
    last_name VARCHAR(255) NOT NULL,
4
    first_name VARCHAR(255),
5
    address VARCHAR(255),
6
    city VARCHAR(255),
7
    PRIMARY KEY (person_id)
8
);

1
CREATE TABLE person (
2
    person_id BIGINT NOT NULL PRIMARY KEY,
3
    last_name VARCHAR(255) NOT NULL,
4
    first_name VARCHAR(255),
5
    address VARCHAR(255),
6
    city VARCHAR(255)
7
);

1
CREATE TABLE agencies (
2
  id SERIAL PRIMARY KEY,
3
  name TEXT NOT NULL
4
);
5

6
CREATE TABLE users (
7
  id SERIAL PRIMARY KEY,
8
  agency_id INTEGER NOT NULL REFERENCES agencies(id) DEFERRABLE INITIALLY DEFERRED
9
);

1
CREATE TABLE sch_test.user_table (
2
  id serial NOT NULL,
3
  username CHARACTER VARYING,
4
  pass CHARACTER VARYING,
5
  first_name CHARACTER varying(30),
6
  last_name CHARACTER varying(30),
7
  CONSTRAINT user_table_pkey PRIMARY KEY (id)
8
);

1
SELECT * FROM schema_name.table_name WHERE <condition>;
2
SELECT field1, field2 FROM schema_name.table_name WHERE <condition>;

1
SELECT * FROM schema_name.table_name WHERE id = 1;
2
SELECT id FROM schema_name.table_name WHERE username = 'root' AND pass = 'toor';
3
SELECT first_name FROM schema_name.table_name WHERE id != 1;

1
SELECT CHAR_LENGTH('ABCDE');
2
SELECT CHARACTER_LENGTH('ABCDE');

1
5

1
SELECT COALESCE(NULL, NULL, 'HELLO WORLD');
2
SELECT COALESCE(NULL, NULL, 'first non null', NULL, NULL, 'second non null');
3
SELECT COALESCE(NULL, NULL, NULL);

1
CREATE TABLE copy_test(id INT, name varchar(8));

1
COPY copy_test FROM '/path/to/file/sample_data.csv' DELIMITER ',';

1
COPY copy_test FROM STDIN DELIMITER ',';

1
COPY copy_test TO 'path/to/file/sample_data.csv' DELIMITER ',';

1
INSERT INTO person (name, age) VALUES
2
  ('john doe', 25),
3
  ('jane doe', 20);

1
CREATE TABLE my_table (
2
  id serial NOT NULL,
3
  name CHARACTER VARYING,
4
  contact_number INTEGER,
5
  CONSTRAINT my_table_pkey PRIMARY KEY (id)
6
);
7

8
INSERT INTO my_table(name, contact_number)
9
VALUES ('USER', 8542621)
10
RETURNING id;

1
CREATE TABLE person (
2
    person_id BIGINT,
3
    name VARCHAR(255),
4
    age INT,
5
    city VARCHAR(255)
6
);
7

8
INSERT INTO person VALUES (1, 'john doe', 25, 'new york');
9
INSERT INTO person (name, age) VALUES ('john doe', 25);

1
INSERT INTO person
2
SELECT * FROM tmp_person WHERE age < 30;

1
INSERT INTO my_table (name, contact_number)
2
VALUES ('one', 333)
3
RETURNING id;
4

5
INSERT INTO my_table VALUES (2, 'one', 333)
6
ON CONFLICT (id) DO UPDATE
7
SET name = my_table.name || ' changed to: "two" at ' || now()
8
RETURNING *;

1
COPY my_table TO '/home/postgres/my_table.txt'
2
USING DELIMITERS '|'
3
WITH NULL AS 'null_string' CSV HEADER;

1
UPDATE person
2
SET state_code = cities.state_code
3
FROM cities
4
WHERE cities.city = city;

1
UPDATE person SET planet = 'Earth';

1
UPDATE person SET state = 'NY' WHERE city = 'New York';

1
UPDATE person
2
SET country = 'USA',
3
    state = 'NY'
4
WHERE city = 'New York';

1
DROP DATABASE IF EXISTS books_db;
2
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;
3

4
DROP TABLE IF EXISTS books;
5
CREATE TABLE books (
6
  id SERIAL PRIMARY KEY,
7
  client TEXT NOT NULL,
8
  data JSONb NOT NULL
9
);

1
INSERT INTO books(client, data) VALUES
2
(
3
  'Joe',
4
  '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
5
),
6
(
7
  'Jenny',
8
  '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
9
),
10
(
11
  'Jenny',
12
  '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
13
);

1
SELECT client, data->'title' AS title FROM books;
2
SELECT client, data->'title' AS title, data->'author' AS author FROM books;
3
SELECT client, data->'author'->'last_name' AS author FROM books;

1
SELECT client, data->'title' AS title
2
FROM books
3
WHERE data->'title' = '"Dharma Bums"';
4

5
SELECT client, data->'title' AS title
6
FROM books
7
WHERE data->'author'->>'last_name' = 'Kerouac';

1
CREATE TABLE events (
2
  name varchar(200),
3
  visitor_id varchar(200),
4
  properties json,
5
  browser json
6
);

1
SELECT browser->>'name' AS browser,
2
       COUNT(browser)
3
FROM events
4
GROUP BY browser->>'name';
5

6
SELECT visitor_id,
7
       SUM(CAST(properties->>'amount' AS INTEGER)) AS total
8
FROM events
9
WHERE CAST(properties->>'amount' AS INTEGER) > 0
10
GROUP BY visitor_id;
11

12
SELECT AVG(CAST(browser->'resolution'->>'x' AS INTEGER)) AS width,
13
       AVG(CAST(browser->'resolution'->>'y' AS INTEGER)) AS height
14
FROM events;

1
CREATE TABLE mytable (data JSONB NOT NULL);
2
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

1
SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';
2
SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["alice1@test.com"]}';
3
SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';
4
SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

1
SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
2
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
3
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

1
CREATE TABLE mytable (data JSONB NOT NULL);
2
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

1
SELECT MIN(age), MAX(age), AVG(age)
2
FROM individuals;

1
CREATE TABLE heap_histogram (
2
    histwhen TIMESTAMP WITHOUT TIME ZONE NOT NULL,
3
    class CHARACTER VARYING NOT NULL,
4
    bytes INTEGER NOT NULL
5
);
6

7
SELECT class,
8
       REGR_SLOPE(bytes, EXTRACT(epoch FROM histwhen)) AS slope
9
FROM public.heap_histogram
10
GROUP BY class
11
HAVING REGR_SLOPE(bytes, EXTRACT(epoch FROM histwhen)) > 0
12
ORDER BY slope DESC;

1
SELECT STRING_AGG(name, ', ') AS names, country
2
FROM individuals
3
GROUP BY country;

1
WITH sales AS (
2
  SELECT
3
    orders.ordered_at,
4
    orders.user_id,
5
    SUM(orders.amount) AS total
6
  FROM orders
7
  GROUP BY orders.ordered_at, orders.user_id
8
)
9
SELECT
10
  sales.ordered_at,
11
  sales.total,
12
  users.name
13
FROM sales
14
JOIN users USING (user_id);

1
CREATE TABLE empl (
2
    name TEXT PRIMARY KEY,
3
    boss TEXT NULL REFERENCES empl(name)
4
        ON UPDATE CASCADE
5
        ON DELETE CASCADE
6
        DEFAULT NULL
7
);
8

9
WITH RECURSIVE t(level, path, boss, name) AS (
10
    SELECT 0, name, boss, name FROM empl WHERE boss IS NULL
11
    UNION
12
    SELECT
13
        level + 1,
14
        path || ' > ' || empl.name,
15
        empl.boss,
16
        empl.name
17
    FROM empl JOIN t ON empl.boss = t.name
18
)
19
SELECT * FROM t ORDER BY path;

1
CREATE TABLE wf_example(i INT, t TEXT, ts timestamptz, b BOOLEAN);
2

3
SELECT *
4
  , DENSE_RANK() OVER (ORDER BY i) dist_by_i
5
  , LAG(t) OVER () prev_t
6
  , NTH_VALUE(i, 6) OVER () nth
7
  , COUNT(TRUE) OVER (PARTITION BY i) num_by_i
8
  , COUNT(TRUE) OVER () num_all
9
  , NTILE(3) OVER () ntile
10
FROM wf_example;

1
SELECT i,
2
       DENSE_RANK() OVER (ORDER BY i),
3
       ROW_NUMBER() OVER (),
4
       RANK() OVER (ORDER BY i)
5
FROM wf_example;

1
WITH RECURSIVE t(n) AS (
2
    VALUES (1)
3
  UNION ALL
4
    SELECT n + 1 FROM t WHERE n < 100
5
)
6
SELECT SUM(n) FROM t;

1
CREATE FUNCTION active_subscribers() RETURNS BIGINT AS $$
2
DECLARE
3
    subscribers INTEGER;
4
BEGIN
5
    SELECT COUNT(user_id) INTO subscribers
6
    FROM users
7
    WHERE subscribed;
8

9
    RETURN subscribers;
10
EXCEPTION
11
    WHEN undefined_table THEN
12
        RETURN NULL;
13
END;
14
$$ LANGUAGE plpgsql;
15

16
SELECT active_subscribers();

1
CREATE OR REPLACE FUNCTION s164() RETURNS void AS $$
2
BEGIN
3
  RAISE EXCEPTION USING
4
    message = 'S 164',
5
    detail = 'D 164',
6
    hint = 'H 164',
7
    errcode = 'P2222';
8
END;
9
$$ LANGUAGE plpgsql;

1
CREATE OR REPLACE FUNCTION s165() RETURNS void AS $$
2
BEGIN
3
  RAISE EXCEPTION '%', 'nothing specified';
4
END;
5
$$ LANGUAGE plpgsql;

1
CREATE [OR REPLACE] FUNCTION functionName (someParameter parameterType)
2
RETURNS DATATYPE AS $_block_name_$
3
DECLARE
4
    -- deklarasi
5
BEGIN
6
    -- proses
7
    -- return
8
END;
9
$_block_name_$ LANGUAGE plpgsql;

1
CREATE TABLE users (username TEXT, email TEXT);
2
CREATE TABLE simple_users () INHERITS (users);
3
CREATE TABLE users_with_password (password TEXT) INHERITS (users);

1
COPY (SELECT oid, relname FROM pg_class LIMIT 5) TO STDOUT;

1
COPY products(is_public, title, discount)
2
TO 'D:\csv_backup\products_db.csv'
3
DELIMITER ',' CSV HEADER;
4

5
COPY categories(name)
6
TO 'D:\csv_backup\categories_db.csv'
7
DELIMITER ',' CSV HEADER;

1
COPY products
2
TO 'D:\csv_backup\products_db.csv'
3
DELIMITER ',' CSV HEADER;
4

5
COPY categories
6
TO 'D:\csv_backup\categories_db.csv'
7
DELIMITER ',' CSV HEADER;

1
CREATE TABLE company (
2
    id SERIAL PRIMARY KEY NOT NULL,
3
    name TEXT NOT NULL,
4
    created_at TIMESTAMP,
5
    modified_at TIMESTAMP DEFAULT NOW()
6
);
7

8
CREATE TABLE log (
9
    id SERIAL PRIMARY KEY NOT NULL,
10
    table_name TEXT NOT NULL,
11
    table_id TEXT NOT NULL,
12
    description TEXT NOT NULL,
13
    created_at TIMESTAMP DEFAULT NOW()
14
);

1
CREATE OR REPLACE FUNCTION add_created_at_function()
2
RETURNS TRIGGER AS $BODY$
3
BEGIN
4
  NEW.created_at := NOW();
5
  RETURN NEW;
6
END
7
$BODY$ LANGUAGE plpgsql;
8

9
CREATE TRIGGER add_created_at_trigger
10
BEFORE INSERT ON company
11
FOR EACH ROW
12
EXECUTE PROCEDURE add_created_at_function();

1
CREATE OR REPLACE FUNCTION add_log_function()
2
RETURNS TRIGGER AS $BODY$
3
DECLARE
4
  vDescription TEXT;
5
  vId INT;
6
  vReturn RECORD;
7
BEGIN
8
  vDescription := TG_TABLE_NAME || ' ';
9

10
  IF (TG_OP = 'INSERT') THEN
11
    vId := NEW.id;
12
    vDescription := vDescription || 'added. Id: ' || vId;
13
    vReturn := NEW;
14
  ELSIF (TG_OP = 'UPDATE') THEN
15
    vId := NEW.id;
16
    vDescription := vDescription || 'updated. Id: ' || vId;
17
    vReturn := NEW;
18
  ELSIF (TG_OP = 'DELETE') THEN
19
    vId := OLD.id;
20
    vDescription := vDescription || 'deleted. Id: ' || vId;
21
    vReturn := OLD;
22
  END IF;
23

24
  INSERT INTO log(table_name, table_id, description, created_at)
25
  VALUES (TG_TABLE_NAME, vId, vDescription, NOW());
26

27
  RETURN vReturn;
28
END
29
$BODY$ LANGUAGE plpgsql;
30

31
CREATE TRIGGER add_log_trigger
32
AFTER INSERT OR UPDATE OR DELETE ON company
33
FOR EACH ROW
34
EXECUTE PROCEDURE add_log_function();

1
CREATE OR REPLACE FUNCTION my_simple_trigger_function()
2
RETURNS TRIGGER AS $BODY$
3
BEGIN
4
  IF (TG_TABLE_NAME = 'users') THEN
5
    IF (TG_OP = 'INSERT') THEN
6
      INSERT INTO log_table(date_and_time, description)
7
      VALUES (NOW(), 'New user inserted. User ID: ' || NEW.id);
8
      RETURN NEW;
9
    ELSIF (TG_OP = 'DELETE') THEN
10
      INSERT INTO log_table(date_and_time, description)
11
      VALUES (NOW(), 'User deleted. User ID: ' || OLD.id);
12
      RETURN OLD;
13
    END IF;
14
    RETURN NULL;
15
  END IF;
16
END;
17
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
18

19
CREATE TRIGGER my_trigger
20
AFTER INSERT OR DELETE ON users
21
FOR EACH ROW
22
EXECUTE PROCEDURE my_simple_trigger_function();

1
CREATE TABLE tab_event_logs(
2
  date_time TIMESTAMP,
3
  event_name TEXT,
4
  remarks TEXT
5
);
6

7
CREATE OR REPLACE FUNCTION fn_log_event()
8
RETURNS EVENT_TRIGGER
9
LANGUAGE SQL
10
AS $main$
11
  INSERT INTO tab_event_logs(date_time, event_name, remarks)
12
  VALUES (NOW(), TG_TAG, 'Event Logging');
13
$main$;
14

15
CREATE EVENT TRIGGER trg_log_event
16
ON DDL_COMMAND_START
17
EXECUTE PROCEDURE fn_log_event();

1
CREATE ROLE niceusername WITH PASSWORD 'very-strong-password' LOGIN;

1
CREATE ROLE niceusername WITH LOGIN;
2
\password niceusername

1
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
2
GRANT CONNECT ON DATABASE nova TO user_name;

1
REVOKE ALL ON SCHEMA public FROM PUBLIC;
2
GRANT USAGE ON SCHEMA public TO user_name;

1
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
2
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
3
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
4
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;

1
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
2
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only;
3
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write;
4
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;
5
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin;

1
host    sameuser        all     localhost    md5
2
local   sameuser        all                  md5

1
SHOW search_path;
2
ALTER USER user1 SET search_path='my_schema, "$user", public';
3
SHOW search_path;

1
SET ROLE user1;
2
SHOW search_path;

1
CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
2
GRANT CONNECT ON DATABASE <database_name> TO readonly;
3
GRANT USAGE ON SCHEMA public TO readonly;
4
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
5
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

1
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
2
GRANT SELECT ON TABLES TO read_only;
3

4
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
5
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLES TO read_write;
6

7
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
8
GRANT ALL ON TABLES TO admin;
9

10
ALTER DEFAULT PRIVILEGES FOR ROLE admin
11
GRANT SELECT ON TABLES TO read_only;

1
CREATE EXTENSION pgcrypto;

1
SELECT DIGEST('1', 'sha1');
2
SELECT DIGEST(CONCAT(CAST(CURRENT_TIMESTAMP AS TEXT), RANDOM()::TEXT), 'sha1');

1
COMMENT ON TABLE table_name IS 'this is student details table';

1
COMMENT ON TABLE student IS NULL;

1
SELECT pg_start_backup('my-backup');
2
SELECT pg_stop_backup();

1
COPY <tablename> FROM '<filename with path>';
2
COPY user FROM '/home/user/user_data.csv';
3
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
4
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;

1
COPY <tablename> TO STDOUT (DELIMITER '|');
2
COPY user TO STDOUT (DELIMITER '|');
3
COPY (SELECT * FROM user WHERE user_name LIKE 'A%') TO '/home/user/user_data';
4
COPY user TO PROGRAM 'gzip > /home/user/user_data.gz';

1
#!/bin/sh
2
cd /save_db
3
DATE=$(date +%d-%m-%Y-%Hh%M)
4
echo -e "Sauvegarde de la base du ${DATE}"
5
mkdir prodDir${DATE}
6
cd prodDir${DATE}
7

8
# dump file
9
/opt/postgres/9.0/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b -w -v -f "dbprod${DATE}.backup" dbprod
10

11
# SQL file
12
/opt/postgres/9.0/bin/pg_dump -i -h localhost -p 5432 -U postgres --format plain --verbose -f "dbprod${DATE}.sql" dbprod

1
import psycopg2
2

3
db_host = 'postgres.server.com'
4
db_port = '5432'
5
db_un = 'user'
6
db_pw = 'password'
7
db_name = 'testdb'
8

9
conn = psycopg2.connect(
10
    "dbname={} host={} user={} password={}".format(db_name, db_host, db_un, db_pw)
11
)
12
cur = conn.cursor()
13
sql = 'select * from testtable where id > %s and id < %s'
14
args = (1, 4)
15
cur.execute(sql, args)
16
print(cur.fetchall())

1
var connString = "Host=myserv;Username=myuser;Password=mypass;Database=mydb";
2
using (var conn = new NpgsqlConnection(connString))
3
{
4
    var querystring = "INSERT INTO data (some_field) VALUES (@content)";
5
    conn.Open();
6
    using (var cmd = new NpgsqlCommand(querystring, conn))
7
    {
8
        var contentString = "Hello World!";
9
        cmd.Parameters.Add("@content", NpgsqlDbType.Text).Value = contentString;
10
        cmd.ExecuteNonQuery();
11
    }
12
}

1
private static java.sql.Connection connect(String url, String user, String password)
2
    throws ClassNotFoundException, java.sql.SQLException {
3

4
    Class.forName("org.postgresql.Driver");
5
    return java.sql.DriverManager.getConnection(url, user, password);
6
}

1
private static java.sql.Connection connect(String url, String user, String password)
2
    throws ClassNotFoundException, java.sql.SQLException {
3

4
    Class.forName("org.postgresql.Driver");
5

6
    java.util.Properties props = new java.util.Properties();
7
    props.setProperty("user", user);
8
    props.setProperty("password", password);
9
    props.setProperty("prepareThreshold", "0");
10

11
    return java.sql.DriverManager.getConnection(url, props);
12
}

1
private static javax.sql.DataSource createDataSource(String url, String user, String password) {
2
    org.postgresql.ds.PGPoolingDataSource ds = new org.postgresql.ds.PGPoolingDataSource();
3
    ds.setUrl(url);
4
    ds.setUser(user);
5
    ds.setPassword(password);
6
    ds.setInitialConnections(10);
7
    ds.setMaxConnections(20);
8
    ds.setSslMode("require");
9
    ds.setSslfactory("org.postgresql.ssl.NonValidatingFactory");
10
    return ds;
11
}

1
java.sql.Connection conn = ds.getConnection();
2
// lakukan pekerjaan
3
conn.close(); // mengembalikan koneksi ke pool

1
host    replication    replication    <slave-IP>/32    md5

1
wal_level = hot_standby
2
archive_mode = on
3
archive_command = 'test ! -f /path/to/archivedir/%f && cp %p /path/to/archivedir/%f'
4
wal_senders = 5

1
hot_standby = on

1
standby_mode = on
2
primary_conninfo = 'host=<PRIMARY_IP> port=5432 user=replication password=<PASSWORD>'
3
trigger_file = '/tmp/postgresql.trigger.5432'

1
CREATE EXTENSION postgres_fdw;
2

3
CREATE SERVER name_srv
4
FOREIGN DATA WRAPPER postgres_fdw
5
OPTIONS (host 'hostname', dbname 'bd_name', port '5432');
6

7
CREATE USER MAPPING FOR postgres
8
SERVER name_srv
9
OPTIONS (user 'postgres', password 'password');
10

11
CREATE FOREIGN TABLE table_foreign (
12
  id INTEGER,
13
  code CHARACTER VARYING
14
)
15
SERVER name_srv
16
OPTIONS (schema_name 'schema', table_name 'table');
17

18
SELECT * FROM table_foreign;

1
CREATE EXTENSION postgres_fdw;
2

3
CREATE SERVER server_name
4
FOREIGN DATA WRAPPER postgres_fdw
5
OPTIONS (host 'host_ip', dbname 'db_name', port 'port_number');
6

7
CREATE USER MAPPING FOR CURRENT_USER
8
SERVER server_name
9
OPTIONS (user 'user_name', password 'password');
10

11
CREATE SCHEMA schema_name;
12

13
IMPORT FOREIGN SCHEMA schema_name_to_import_from_remote_db
14
FROM SERVER server_name
15
INTO schema_name;
16

17
SELECT * FROM schema_name.table_name;