Re: best migration solution
Hello Markus, keep it simple. Use a restored backup of the source db or this db itself and then Am 25.04.2024 um 09:55 schrieb Zwettler Markus (OIZ): we have to migrate from hosted PG12 to containerized PG16 on private cloud. some of the installed PG12 extensions are not offered on the containerized PG16, eg. PostGIS related extensions like pg_routing and ogr_fdw. some of these extensions are not needed anymore. some of these extensions were installed in their own schema. we also need to change the database names and most role names due to external requirements. I came up with this solution. dump all roles with pg_dumpall. edit this dumpfile and * exclude roles not needed drop roles not needed * change required role names rename the required roles to their new names then dump the roles dump all required databases with pg_dump * in plain text * exclude all schemas not needed drop all schemas not needed as well as any extension that does not exist on the target and those that have own schemas (maybe they should not be installed before the dump is imported) then take a pg_dump just of the database(s) * edit this dump file and * exclude any "create extension" command for not existing extensions * change all required role names on permissions and ownerships any missings? any better solutions? I wonder whether a plain text dump could lead to conversion problems or something similar? when the roles and db-dump are imported, install the missing extensions. To take the dumps use the binaries of the target version kind regards and good luck Georg
Re: Converting sql anywhere to postgres
Hi,
Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I
have a field called mod_user in a table, but when I do an update on
the table and do not set mod_user then SQL Anywhere sets the field to
current_uer. I have tried to replicate this using a postgres trigger
in the before update. However, if I do not set the value then it
automatically picks up the value that was already in the field. Is
there a way to tell the difference between me setting the value to the
same as the previous value and postgres automatically picking it up.
If the field myfield contains the word ‘me’. Can I tell the difference
between:
Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
maybe this is what you're looking for (without a trigger)
CREATE TABLE mytest.autovalues
(
key serial NOT NULL,
product text,
updated_by text DEFAULT current_user,
updated_at timestamp without time zone DEFAULT current_timestamp,
PRIMARY KEY (key)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS mytest.autovalues
OWNER to postgres;
-- instead of current_user you may also use |session_user see
https://www.postgresql.org/docs/current/functions-info.html|
|
|
|then try:
|
|insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values
('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
select * from mytest.autovalues;|
|
|
|In case you want to "automate" the update command (not setting
|||updated_by to DEFAULT manually/programmatically)| you may use an on
update trigger that compares current_user/session_user with
old.|updated_by and if they are different you could set new.updated_by
to DEFAULT (or whatever logic fits your needs)||
kind regards
Georg
Re: psql "\d" no longer working
Hello Rob, Am 12.02.2023 um 10:02 schrieb Rob Sargent: Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works riftehr=> \dt act* List of relations Schema | Name | Type | Owner +---+---+--- cell | actual_and_inf_rel_clean_final | table | cell cell | actual_and_inf_rel_clean_final_count_rels | table | cell cell | actual_and_inf_rel_part1 | table | cell cell | actual_and_inf_rel_part1_unique | table | cell cell | actual_and_inf_rel_part1_unique_clean | table | cell cell | actual_and_inf_rel_part2 | table | cell cell | actual_and_inf_rel_part2_unique | table | cell cell | actual_and_inf_rel_part2_unique_clean | table | cell (8 rows) riftehr=> select version(); version - PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit (1 row) The server was restarted Friday morning (according to systemctl) and the log file has the complete sql statement: 2023-02-10 13:42:55.214 MST [524159] STATEMENT: SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '219319'; 2023-02-10 13:43:01.143 MST [524159] ERROR: column c.relhasoids does not exist at character 80 but I don't see any other issue in the log file. I have yet to find another broken meta-command and no sql of mine has failed along similar lines as had "\d" Any pointers much appreciated. Check the version of your psql binary. I assume it's below v13. There was a change in pg_catalog. Clients below 13 assume, the column is still there. kind regards Georg
Re: PostgreSQL reference coffee mug
Hello Matthias, Am 28.07.2021 um 15:40 schrieb Matthias Apitz: El día martes, julio 27, 2021 a las 08:32:45p. m. +0200, Matthias Apitz escribió: Thank you, Pavel. This is ofc to much for a coffee mug. For using it as a Reference Card in paper form, it's a pity that it is not written in English. I'm working on my own for the mug and will publish the PDF and libreoffice ODT version here. The max size of the image for the mug is 7.5cm high x 16cm around the body of the coffee mug. only typos: \o file snnds -> sends \passwor -> password createdb ... dbna -> dbname Attached is a first version as PDF. Bugs/comments are welcome. Thanks. matthias regards Georg
Re: short sql question
Hi, Am 18.06.2021 um 09:15 schrieb goldgraeber-werbetech...@t-online.de: Hi, I just cannot see what is wrong with my query: create table files (id int, name text, prev_name text, ); create table fnchanged (id int, name text); update files f set prev_name = f.name, name = c.name from fnchanges c where f.id = c.id and c.name != f.name your update statement works for me after changing from fnchanges to from fnchanged (as written in your create statement) but I've gotERROR: relation "fnchanges" does not exist instead of a syntax error --- gets syntax error at "from" (Using PostgreSQL 10.4 on a NAS box) Best regards Wolfgang kind regards Georg
Re: Question on pgwatch
Hi Bikram, Am 23.08.2019 um 22:10 schrieb Bikram Majumdar: Hi George, So nice for your response. Thanks. But, my question is how does it get the IP address 172.17.0.2 ? And, how does one run psql command to connect/login to the test database ( pgwatch configuration database) to add any database ? Thanks and regards, Bikram the pgwatch2 configuration database resides within the docker container when using the docker variant. Whether you've exposed this port on container creation/start or you have to go into the docker image (docker exec -it IMAGENAME /bin/bash) and connect there with psql (pg_hba.conf should be in /etc/postgresql/[version]/main or similiar). regards Georg
Re: Question on pgwatch
Hello Bikram, Am 22.08.2019 um 23:50 schrieb Bikram MAJUMDAR: Hi, Need help from anyone in the team who has worked on pgwatch2. Yesterday we installed pgwatch2 docker image and started the container. We are running the pgwatch2 container from the database server itself - Now we want to add our databases for monitoring using the admin interface: We are opening the pgwatch2 admin interface at :8080/dbs.But, when we try to add the database (cemtore) in the admin interface we get the following error: Could not connect to specified host (ignore if gatherer daemon runs on another host): FATAL: no pg_hba.conf entry for host "172.17.0.2", user "cemtore", database "cemtore", SSL off Any idea what we are doing wrong? We would now like to add the database manually following notes below: Usage by default the pgwatch2 configuration database running inside Docker is being monitored so that you can immediately see some graphs, but you should add new databases by opening the admin interface at 127.0.0.1:8080/dbs or logging into the Postgres config DB and inserting into pgwatch2.monitored_db table (db - pgwatch2 , default user/pw - pgwatch2/pgwatch2admin) But, my question is, how do I run the psql on my database server to login to this default pgwatch2 configuration DB? Bikram Majumdar Sr Software Developer/DBA, Aqsacom Inc. c. 1.972.365.3737 As the message states (FATAL: no pg_hba.conf entry for host "172.17.0.2", user "cemtore", database "cemtore", SSL off) you have to edit pg_hba.conf of the PostgreSQL Cluster hosting the cemtore database to allow connections from pgwatch to it as your current config doesn't allow that. Also you may need the connecting user within your database with the required rights (if not added yet). regards Georg
Re: logging "raise" to file
Hi, Am 28.07.2019 um 12:32 schrieb wambac...@posteo.de: Hi, is there a way to log output from "raise ..." to a local file? \o file does not work (for me). regards walter the red part writes your "raise notice" to your log psql (+your connection string) -f /path/to/file.sql *> /path/to/log/xxx.log 2>&1* see https://dba.stackexchange.com/questions/107199/how-to-log-custom-messages-from-inside-a-postgresql-transaction regards Georg
