[POSTGRESQL] CHEATSHEET IMPORT DB - fourslickz/notes GitHub Wiki
CREATE DATABASE pramuka_gamification_db_prod;
CREATE USER pramuka_gamification_user WITH ENCRYPTED PASSWORD 'XXXXXXXX';
GRANT CONNECT ON DATABASE pramuka_gamification_db_prod TO pramuka_gamification_user;
\c pramuka_gamification_db_prod
CREATE SCHEMA pramuka_gamification_db_prod AUTHORIZATION pramuka_gamification_user;
ALTER ROLE pramuka_gamification_user SET search_path TO pramuka_gamification_db_prod;
ALTER SCHEMA pramuka_gamification_db_prod OWNER TO pramuka_gamification_user;
sed -i '/^\\\\restrict/d' postgresql-pramuka_gamification_db_prod-20260525-200001.sql
sed -i '/^\\\\unrestrict/d' postgresql-pramuka_gamification_db_prod-20260525-200001.sql
sed -i '/OWNER TO ferdi/d' postgresql-pramuka_gamification_db_prod-20260525-200001.sql
sed -i '/GRANT .* TO ferdi/d' postgresql-pramuka_gamification_db_prod-20260525-200001.sql
docker exec -i 9041cd725da9 psql -U postgres -d pramuka_gamification_db_prod < postgresql-pramuka_gamification_db_prod-20260525-200001.sql
root@sandbox-petrolab:/usr/local/src# docker exec -it 9041cd725da9 bash
root@9041cd725da9:/# su - postgres
postgres@9041cd725da9:~$ psql
psql (15.18 (Debian 15.18-1.pgdg13+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
------------------------------+----------+----------+------------+------------+------------+-----------------+--------------------------------------
mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
pramuka_gamification_db_prod | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres +
| | | | | | | pramuka_gamification_user=c/postgres
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(5 rows)
postgres=#
---
postgres@9041cd725da9:~$ psql
psql (15.18 (Debian 15.18-1.pgdg13+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
---------------------------------+----------+----------+------------+------------+------------+-----------------+-----------------------
mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
pramuka_gamification_db_sandbox | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(5 rows)
postgres=# \c pramuka_gamification_db_prod
You are now connected to database "pramuka_gamification_db_prod" as user "postgres".
pramuka_gamification_db_prod=#
pramuka_gamification_db_prod=# \dn
List of schemas
Name | Owner
------------------------------+---------------------------
pramuka_gamification_db_prod | pramuka_gamification_user
public | pg_database_owner
(2 rows)
pramuka_gamification_db_prod=#
pramuka_gamification_db_prod=# \dt pramuka_gamification_db_prod.*
List of relations
Schema | Name | Type | Owner
------------------------------+------------------------------------+-------+---------------------------
pramuka_gamification_db_prod | _prisma_migrations | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_activities | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_activity_bookmarks | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_activity_participants | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_history_kwartirs | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_leaderboard | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_leaderboard_kwartirs | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_lottery_activities | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_lottery_webinars | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_scout_streaks | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_setting_kwartirs | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_settings | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_user_histories | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_user_points | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_voucher_activities | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_voucher_claims | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_voucher_webinars | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_vouchers | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_webinar_bookmarks | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_webinar_participants | table | pramuka_gamification_user
pramuka_gamification_db_prod | gamification_webinars | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_cart | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_cart_detail | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_cart_history | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_history | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_items | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_items_stock_history | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_payment | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_pricing | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_status | table | pramuka_gamification_user
pramuka_gamification_db_prod | ref_gems_user | table | pramuka_gamification_user
(31 rows)
pramuka_gamification_db_prod=#
pramuka_gamification_db_prod=# SELECT * FROM pramuka_gamification_db_prod.gamification_history_kwartirs;
pramuka_gamification_db_prod=#