1
sudo apt install postgresql-13-pglogical

1
sudo su - postgres -c "gsutil cp gs://cloud-training/gsp918/pg_hba_append.conf ."
2
sudo su - postgres -c "gsutil cp gs://cloud-training/gsp918/postgresql_append.conf ."
3
sudo su - postgres -c "cat pg_hba_append.conf >> /etc/postgresql/13/main/pg_hba.conf"
4
sudo su - postgres -c "cat postgresql_append.conf >> /etc/postgresql/13/main/postgresql.conf"
5
sudo systemctl restart postgresql@13-main

1
sudo su - postgres
2
psql

1
\c postgres;
2
CREATE EXTENSION pglogical;
3
\c orders;
4
CREATE EXTENSION pglogical;

1
CREATE USER {user} PASSWORD 'DMS_1s_cool!';
2
ALTER DATABASE orders OWNER TO {user};
3
ALTER ROLE {user} WITH REPLICATION;

1
\c postgres;
2
GRANT USAGE ON SCHEMA pglogical TO {user};
3
GRANT ALL ON SCHEMA pglogical TO {user};
4
GRANT SELECT ON pglogical.tables TO {user};
5
GRANT SELECT ON pglogical.depend TO {user};
6
GRANT SELECT ON pglogical.local_node TO {user};
7
GRANT SELECT ON pglogical.local_sync_status TO {user};
8
GRANT SELECT ON pglogical.node TO {user};
9
GRANT SELECT ON pglogical.node_interface TO {user};
10
GRANT SELECT ON pglogical.queue TO {user};
11
GRANT SELECT ON pglogical.replication_set TO {user};
12
GRANT SELECT ON pglogical.replication_set_seq TO {user};
13
GRANT SELECT ON pglogical.replication_set_table TO {user};
14
GRANT SELECT ON pglogical.sequence_state TO {user};
15
GRANT SELECT ON pglogical.subscription TO {user};
16

17
\c orders;
18
GRANT USAGE ON SCHEMA pglogical TO {user};
19
GRANT ALL ON SCHEMA pglogical TO {user};
20
GRANT SELECT ON pglogical.tables TO {user};
21
GRANT SELECT ON pglogical.depend TO {user};
22
GRANT SELECT ON pglogical.local_node TO {user};
23
GRANT SELECT ON pglogical.local_sync_status TO {user};
24
GRANT SELECT ON pglogical.node TO {user};
25
GRANT SELECT ON pglogical.node_interface TO {user};
26
GRANT SELECT ON pglogical.queue TO {user};
27
GRANT SELECT ON pglogical.replication_set TO {user};
28
GRANT SELECT ON pglogical.replication_set_seq TO {user};
29
GRANT SELECT ON pglogical.replication_set_table TO {user};
30
GRANT SELECT ON pglogical.sequence_state TO {user};
31
GRANT SELECT ON pglogical.subscription TO {user};
32

33
GRANT USAGE ON SCHEMA public TO {user};
34
GRANT ALL ON SCHEMA public TO {user};
35
GRANT SELECT ON public.distribution_centers TO {user};
36
GRANT SELECT ON public.inventory_items TO {user};
37
GRANT SELECT ON public.order_items TO {user};
38
GRANT SELECT ON public.products TO {user};
39
GRANT SELECT ON public.users TO {user};

1
\c orders;
2
\dt
3
ALTER TABLE public.distribution_centers OWNER TO {user};
4
ALTER TABLE public.inventory_items OWNER TO {user};
5
ALTER TABLE public.order_items OWNER TO {user};
6
ALTER TABLE public.products OWNER TO {user};
7
ALTER TABLE public.users OWNER TO {user};
8
\dt

1
ALTER TABLE inventory_items ADD PRIMARY KEY (id);