SELECT
pid,
now() - query_start AS duration,
query,
state
, query_start
, wait_event
, wait_event_type
, client_addr
, usename
, datname
--, concat('select pg_terminate_backend(',pid::varchar,');')
FROM odoo_pg_stat_activity
where usename ='bi'
--WHERE (now() - query_start) > interval '5 minutes';
order by duration desc;
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'odoo2'
;
CREATE DATABASE odoo2 TEMPLATE odoo;
SELECT
schemaname as table_schema, relname as table_name, n_live_tup as row_count
FROM
pg_stat_user_tables
ORDER BY
n_live_tup DESC;
SELECT table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
FROM (
SELECT table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
FROM information_schema.tables
WHERE table_schema = 'public' --<< change here for the schema you want
) t ORDER BY 3 DESC
SELECT * FROM information_schema.role_table_grants WHERE table_schema='public';
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
FROM "names";
SELECT * from information_schema.tables where table_type='FOREIGN' and table_name ='test';
SELECT
srvname AS name,
srvowner::regrole AS owner,
fdwname AS wrapper,
srvoptions AS options
FROM pg_foreign_server
JOIN pg_foreign_data_wrapper w ON w.oid = srvfdw;
SELECT * FROM pg_user_mapping;
SELECT case when ordinal_position=1 then concat('drop foreign table if exists ',c.table_schema,'_' ,c.table_name,';') else '' end AS strdrop,
case when ordinal_position=1 then concat('create FOREIGN TABLE ',c.table_schema,'_' ,c.table_name,'(') else '' end ||
concat(
case when ordinal_position=1 then '' else ',' end,
'"',column_name,'"'
' ',
case udt_name
when 'varchar' then
case
when character_maximum_length is not null then concat(udt_name,'(',character_maximum_length,')')
else udt_name
end
when 'numeric' then
case
when numeric_precision is not null then concat(udt_name,'(',numeric_precision,',' ,numeric_scale,')' )
else udt_name
end
else udt_name
end,
case when is_nullable='NO' then ' NOT NULL' else '' end
)
||
case when ordinal_position=ma.mo then concat(')SERVER fdw_psql_odoo OPTIONS (schema_name ''',c.table_schema,''', table_name ''',c.table_name,''');') else '' end as strend
--,*
FROM information_schema.columns c
join (
select table_catalog, table_schema, table_name, max(ordinal_position) mo
FROM information_schema.columns
group by table_catalog, table_schema, table_name
) ma on c.table_catalog =ma.table_catalog and c.table_schema =ma.table_schema and c.table_name =ma.table_name
where c.table_schema ='public'
--and c.table_name ='account_account'
order by c.table_catalog , c.table_schema , c.table_name, c.ordinal_position
SELECT case when ordinal_position=1 then concat('drop foreign table if exists ',c.table_schema,'_' ,c.table_name,';') else '' end AS strdrop,
concat(case when ordinal_position=1 then concat('create FOREIGN TABLE ',c.table_schema,'_' ,c.table_name, '(') else '' end,
concat(
case when ordinal_position=1 then '' else ',' end,
'"',column_name,'"'
' ',
case when data_type like '%int' then data_type
when data_type='enum' then 'varchar(500) '
when data_type='datetime' then 'timestamp '
when data_type='longtext' then 'varchar(4000) '
when data_type='mediumtext' then 'varchar(4000) '
when data_type='tinytext' then 'varchar(4000) '
when data_type='tinyint' then 'int '
when data_type='mediumint' then 'int '
when data_type='double' then 'float '
else column_type
end,
case when is_nullable='NO' then ' NOT NULL' else '' end
)
,case when ordinal_position=ma.mo then concat(')SERVER mysql_fdw_matomo OPTIONS (dbname ''',c.table_schema,''', table_name ''',c.table_name,''');') else '' end) as strcreate
FROM information_schema.columns c
join (
select table_catalog, table_schema, table_name, max(ordinal_position) mo
FROM information_schema.columns
group by table_catalog, table_schema, table_name
) ma on c.table_catalog =ma.table_catalog and c.table_schema =ma.table_schema and c.table_name =ma.table_name
where c.table_schema ='wordpress'
-- and c.table_name ='account_account'
order by c.table_catalog , c.table_schema , c.table_name, c.ordinal_position
mysqldump -u <usuari> -p <base de dades> -h <host\> --password=<password> -P <port> -f ><fitxer sortida>.sql
use <base de dades>
source <path>/<fitxer>.sql
#backup
#docker ps
cd /tmp
A=`docker ps --filter "name=superset_db" -q`
B=`docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $A`
pg_dump -h $B -U superset superset >sortida_superset.sql
#docker exec -it $A sh
#pg_dump -U superset superset >sortida_superset_20211202.sql
#exit
#docker cp $A:/sortida_superset_20211202.sql .
#cp sortida_superset.sql /tmp
exit
exit
scp bi-sc.coopdevs.org:/tmp/sortida_superset.sql .
#restore
cd ~/backup
SRV=bi-coopdevs.local
scp sortida_superset_20211201.sql $SRV:/tmp/sortida.sql
ssh $SRV
sudo su bi
cd
cp /tmp/sortida.sql .
#docker ps #escollir postgres 10
#docker cp sortida.sql 2ff4099ecdef:/sortida.sql
A=`docker ps --filter "name=superset_db" -q`
docker cp sortida.sql $A:/sortida.sql
docker exec -it $A bash
psql -U superset -d postgres
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'superset'
;
drop database superset;
create database superset;
\q
psql -U superset superset < sortida.sql