Divers

Problèmes de démarrage de POSTGRESQL

chown -R postgres:postgres /var/lib/postgresql
chown -R postgres:postgres /etc/postgresql
chown -R postgres:postgres /var/log/postgresql

Problèmes de LOGO

select id,content_type,lo_id from stored_files;
ALTER LARGE OBJECT 131963 OWNER to cyclos;

Corriger les droits POSTGRESQL

for tbl in `psql -qAt -c "SELECT proname FROM pg_proc WHERE pronamespace::regnamespace::text = 'public';" cyclos4` ; do  psql -c "alter function \"$tbl\" owner to cyclos" cyclos4 ; done

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

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

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO cyclos;
GRANT ALL PRIVILEGES ON database cyclos4 TO cyclos;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO cyclos;
ALTER DATABASE cyclos4 OWNER to cyclos;

Pb de réinitialisation de la license cyclos :

update application set license_key = null, license_data = null, local_license_data = null;

update password_types set avoid_obvious = false where id = 1;

update configurations set required_address =false;

Restore la base SQL cyclos :

su - postgres
psql
cyclos4# drop database cyclos4;
cyclos4# create database cyclos4;
cyclos4# exit
psql -d cyclos4 -f /tmp/cyclos4-202101280300.sql
psql -d cyclos4
cyclos4#delete from database_lock;

Problème de démarrage de Cyclos suite à un arrêt inoppiné :

[2021-12-28 17:32:13] [info] ******************************************************************************
[2021-12-28 17:32:13] [info] It seems there is another Cyclos instance connected to this database.
[2021-12-28 17:32:13] [info] If you are sure that is not the case (i.e., the server had crashed),
[2021-12-28 17:32:13] [info] run the following command in the database, then restart Cyclos:
[2021-12-28 17:32:13] [info] delete from database_lock;
[2021-12-28 17:32:13] [info] ******************************************************************************

Purger la base du bugs des balances :

delete from public.closed_account_balances where date > '2021-09-21' ;

Changer l'url par défaut :

cyclos4=# select id,root_url from configurations;
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=1;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=2;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=5;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=6;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=7;

Purge database :

delete from accountbalances where accountid in (select id from accounts where user_id in (select id from users where status ='PURGED'));
delete from accounts where user_id in (select id from users where status ='PURGED');
delete from userstatuslogs where user_id in (select id from users where status ='PURGED');
delete from messagestousers where user_id in (select id from users where status ='PURGED');
delete from adhistorylogs;
delete from notifiedadinterests;
delete from ads where owner_id in (select id from users where status ='PURGED');
delete from users where status = 'PURGED';
delete from entitypropertylogs;
delete from entity_logs;

Changer le mot de passe et le réinitialiser à 1234 :

update passwords set value='$2a$10$yM.uw9jC7C1DrRGUhqUc3eSR6FCJH0.HdDt3CJs8YL56iATHcXH7.' where id=1;

Débloquer un utilisateur :

update passwords set blocked_until = NULL where id=1;

-> Thèmes

-> Default new frontend theme

-> logos

Comment voir la liste des fonctions PGSQL :

SELECT proname,
       proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';

 fn_index_custom_field_value               | cyclos
 fn_create_geography                       | cyclos
 fn_index_ad                               | cyclos
 fn_index_user                             | cyclos
 add_days                                  | cyclos
 add_interval                              | cyclos
 fn_index_address                          | cyclos
 balance_sum                               | cyclos
 get_addresses_tsvector                    | cyclos
 insert_tasks_reindex                      | cyclos
 last_login                                | cyclos
 name_hierarchy                            | cyclos
 order_hierarchy                           | cyclos
 reindex_network                           | cyclos
 strip_html_tags                           | cyclos
 to_geography                              | cyclos
 unix_timestamp                            | cyclos
 reserved_amount_diff                      | cyclos
 account_balance                           | cyclos
 balance_diff                              | cyclos
 current_account_status                    | cyclos
 close_account_balances                    | cyclos
 rebuild_closed_account_balances           | cyclos
 update_account_status                     | cyclos
 account_status_from_closing               | cyclos
 fix_inconsistent_account_balances         | cyclos

Code SQL pour corriger le owner des functions

ALTER FUNCTION fn_index_custom_field_value OWNER TO cyclos;
ALTER FUNCTION fn_create_geography OWNER TO cyclos;
ALTER FUNCTION fn_index_ad OWNER TO cyclos;
ALTER FUNCTION fn_index_user OWNER TO cyclos;
ALTER FUNCTION add_days OWNER TO cyclos;
ALTER FUNCTION add_interval OWNER TO cyclos;
ALTER FUNCTION fn_index_address OWNER TO cyclos;
ALTER FUNCTION balance_sum OWNER TO cyclos;
ALTER FUNCTION get_addresses_tsvector OWNER TO cyclos;
ALTER FUNCTION insert_tasks_reindex OWNER TO cyclos;
ALTER FUNCTION last_login OWNER TO cyclos;
ALTER FUNCTION name_hierarchy OWNER TO cyclos;
ALTER FUNCTION order_hierarchy OWNER TO cyclos;
ALTER FUNCTION reindex_network OWNER TO cyclos;
ALTER FUNCTION strip_html_tags OWNER TO cyclos;
ALTER FUNCTION to_geography OWNER TO cyclos;
ALTER FUNCTION unix_timestamp OWNER TO cyclos;
ALTER FUNCTION reserved_amount_diff OWNER TO cyclos;
ALTER FUNCTION account_balance OWNER TO cyclos;
ALTER FUNCTION balance_diff OWNER TO cyclos;
ALTER FUNCTION current_account_status OWNER TO cyclos;
ALTER FUNCTION close_account_balances OWNER TO cyclos;
ALTER FUNCTION rebuild_closed_account_balances OWNER TO cyclos;
ALTER FUNCTION update_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_from_closing OWNER TO cyclos;
ALTER FUNCTION fix_inconsistent_account_balances OWNER TO cyclos;
ALTER FUNCTION last_account_closing OWNER TO cyclos;
ALTER FUNCTION update_balance OWNER TO cyclos;
ALTER FUNCTION close_balances OWNER TO cyclos;
ALTER FUNCTION last_closed_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_from_cache OWNER TO cyclos;
ALTER FUNCTION insert_missing_balances OWNER TO cyclos;
ALTER FUNCTION inconsistent_closed_accounts OWNER TO cyclos;
ALTER FUNCTION last_closed_account_balances OWNER TO cyclos;
ALTER FUNCTION dirty_account_balances_diffs OWNER TO cyclos;
ALTER FUNCTION cached_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_diffs_since_closing OWNER TO cyclos;
ALTER FUNCTION closed_account_status OWNER TO cyclos;
ALTER FUNCTION insert_missing_account_balances OWNER TO cyclos;
ALTER FUNCTION fix_inconsistent_account_balances OWNER TO cyclos;
ALTER FUNCTION fn_index_translation_message OWNER TO cyclos;
ALTER FUNCTION fn_update_last_balance_closing_date OWNER TO cyclos;
ALTER FUNCTION account_status OWNER TO cyclos;