Re: Query take a long time and use no index

Thanks a lot tomas, i will try it. I have find out that there is a 'aggregation' function in the frontend. But this is MySQL specific and I have no idea the transform it to postgres. It looks like: 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) SELECT channel_id, ? AS type

Query take a long time and use no index

Hello, I use volkszaehler.org (a DIY Smartmeter) on postgres. The hardware is a Dual-core 2,4 GHz amd64-CPU and 6 GB RAM. The databse is approx. 1.5 GB on SSD my local config looks like: root@vz:~# cat /etc/postgresql/15/main/conf.d/local.conf # DB Version: 15 # OS Type: linux # Total Memory (

Move from MySQL to PostgreSQL

'REPLACE INTO'. An other problem is '@prev_timestamp := timestamp' I have no idea how this can rewritten, so that MySQL and PostgreSQL can understood that. For DATE_FORMAT and UNIX_TIMESTAMP I have found wrapper functions. Can someone please help me? Best Regards, Basti

prevent WAL replication to fill filesystem

Hello, I have a old PG 9.6 with WAL replication. for some reason the rsync was failed and the filesystem was filled by pg_xlog files. As result PG stops working. Is there a way to prevent WAL replication to fill filesystem by xlog files in case of failure? Best regards

Re: postgresql order lowercase before uppercase

On 19.03.21 08:33, Laurenz Albe wrote: On Thu, 2021-03-18 at 23:51 +0100, basti wrote: Am 18.03.21 um 17:19 schrieb Laurenz Albe: On Thu, 2021-03-18 at 15:39 +0100, basti wrote: I need to as follow: ande Amma Anit Anti Brac Cali Create an ICU collation: CREATE COLLATION inv

Re: postgresql order lowercase before uppercase

Am 18.03.21 um 17:19 schrieb Laurenz Albe: > On Thu, 2021-03-18 at 15:39 +0100, basti wrote: >> I need to as follow: >> >> ande >> Amma >> Anit >> Anti >> Brac >> Cali >> >> >> I have try ORDER by name COLLATE 'C&#x

Re: postgresql order lowercase before uppercase

This does not help in that case. On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote: Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause? Bobb -Original Message- From: basti Sent: Thursday, March 18, 2021 9:40 AM To: pgsql-gene...@postgresql.org Subject

postgresql order lowercase before uppercase

Hello, I need to as follow: ande Amma Anit Anti Brac Cali I have try ORDER by name COLLATE 'C'; but this order as follow: Amma Anit Anti Brac Cali ande Best Regards

Slow Query, many tmpfiles

Hello, some simple query are very slow since a few days, no significant changes are done the last days. I look for disk-io, swap, looked tables, all seems to be good. What I have found is this: postgres=# SELECT temp_files AS "Temporary files" , temp_bytes AS "Size of temporary files" FROM

Re: How to transfer databases form one server to other

Am 27.01.20 um 21:47 schrieb Adrian Klaver: On 1/26/20 10:56 PM, Andrus wrote: Hi! 3. Manually edit role definitions to delete role postgres since it exists in new server. No need, it will throw a harmless error message and continue on. By my knowledge, pgAdmin executes script in single tr

timescaleDB & WAL replication

Hello, I have a Master / Slave Postgres setup with WAL Replication. Now I want to add timescaleDB. I found this todo: https://docs.timescale.com/latest/tutorials/replication As I understand that in the right way I just need to add the timescaleDB extention on the master side?

Re: Pgadmin III

there is a port of pgadmin3 from bigSQL, support postgresql up to 10. pgadmin3 in debian buster support also postgesql 10. https://metadata.ftp-master.debian.org/changelogs/main/p/pgadmin3/pgadmin3_1.22.2-5_changelog what version of pgadmin3 did you use? pgadmin3_1.22 should support postgresql 9.

Re: Conditional INSERT

NEW.hostname, NEW.txtdata ); On 15.03.19 19:17, Michael Lewis wrote: > On Fri, Mar 15, 2019 at 10:55 AM basti <mailto:mailingl...@unix-solution.de>> wrote: > > Hello, > > I wa

Conditional INSERT

Hello, I want to insert data into table only if condition is true. For example: INSERT into mytable (domainid, hostname, txtdata) VALUES (100,'_acme.challenge.example', 'somedata'); The insert should only be done if Hostname like %_acme.challenge%. How can it be done? I dont want that the us

Re: DB Backup from WAL Slave

4.07.2018 15:33, Andreas Kretschmer wrote: > On 24 July 2018 14:44:45 CEST, basti wrote: >> Hello, >> >> we have a db master and a slave. > > - >> >> How can I do an backup with pg_dumpall from slave? > > Set hot_standby_feedback to on. > > > Regards, Andreas > >

DB Backup from WAL Slave

Hello, we have a db master and a slave. master conf: wal_level = replica max_wal_senders = 5 wal_keep_segments = 100 archive_mode    = on archive_command = 'rsync -a %p -e "ssh -i /var/lib/postgresql/.ssh/id_rsa" postgres@slave:/var/lib/postgresql/9.6/wals/master/%f https://www.postgresql.org/me

Re: psycopg2 and java gssapi questions

Hello, have a look at https://www.postgresql.org/docs/current/static/auth-methods.html There are details about LDAP and GSSAPI. On 20.12.2017 20:42, Mike Feld wrote: > Is it possible to authenticate with Postgres from a standalone > application using gssapi? In other words, I am able to authenti

Re: pg data backup from vps

Replication is no backup. Its more like a RAID. That mean tubles that are delete on master by a mistake there are also delete on slave. correct me if i'am wrong. Best regards, basti On 01.12.2017 21:10, Melvin Davidson wrote: > > > On Fri, Dec 1, 2017 at 3:07 P