Re: alter system command

[Ahmet Demir]

Hi,

Please check postgresql.auto.conf file.
https://www.postgresql.org/docs/13/sql-altersystem.html

Ahmet

On Sun, 27 Dec 2020 at 10:53, Atul Kumar  wrote:

> hi,
>
> I have set archive_command like below:
>
> alter system set archive_command ='pgbackrest --stanza=main archive-push
> %';
>
> then I reloaded the conf file
>
> select pg_reload_conf();
>
>
> now when i checked it using the show command like it gave me correct output
>
> postgres=# show archive_command;
>  archive_command
> -
>  pgbackrest --stanza=main archive-push %
>
>
> but...
>
> while checking the postgresql.conf file it didn't show me above
> output, the parameter acrhive_command is still set with default value
>
> archive_command = '/bin/true/'  # command to use to archive a
> logfile segment
>
> So please help me in giving the reason of it that even after reloading
> the conf file why it didn't set the value in postgresql.conf file ?
>
>
> Regards,
> Atul
>
>
>

Re: PostgreSQL 12 service failing in Ubuntu 20.04 after a few hours

[Ahmet Demir]

And I can suggest checking cron jobs both on root and postgres, killing
those processes and changing root postgres passwords.

Ahmet

On Mon, 2 Jan 2023 at 09:19, Tom Lane  wrote:

> Antonis Christodoulou  <
> vi1p193mb051005c8be974502a0d4a315e1...@vi1p193mb0510.eurp193.prod.outlook.com>
> writes:
> > This is a machine in the cloud, I can’t disconnect it.
>
> In that case, you need to be taking nonzero security precautions.
>
> > And yes the ps looks like this precisely when I do a fresh restart. I
> kill all postgres processes and restart:
> > Then this is the output of me ps:
>
> That looks fine ... but this doesn't:
>
> >>> postgres 3342383   1  0  2022 ?00:00:00 FzXlkULu
> >>> postgres 3344758   1 99  2022 ?3-14:39:11 OElid7Dp
> >>> postgres 3419125   1 18 13:57 ?01:17:03 tracepath
>
> Somebody is hacking into your system and commandeering it to run
> something resource-intensive, possibly a bitcoin miner.  Whatever
> it is, it's trying to obscure its process name which is hardly
> a sign of good intentions.
>
> I'd counsel taking a hard look at your pg_hba.conf to be sure
> it's not allowing non-credentialed logins from anywhere.  And
> for pete's sake don't use a guessable password.
>
> regards, tom lane
>
>
>

Re: TRUNCATE memory leak with temporary tables?

[Ahmet Demir]

Hi Ravi,

I am not sure about that
"It creates a new empty table , followed by rename of the existing table to
the new empty table and finally dropping of the old table."

You mean table is re-created with new oid?

thanks
Ahmet

On Fri, 28 May 2021 at 15:10, Ravi Krishna  wrote:

> Truncate is not delete + vaccum.
> It creates a new empty table , followed by rename of the existing table to
> the new empty table and finally dropping of the old table.
>
> On May 28, 2021 at 7:05 AM, Vijaykumar Jain <
> vijaykumarjain.git...@gmail.com> wrote:
>
> Yes,
> I too see growth when text type is used, but not when int or even fixed
> size char(10) is used.
>
> I always thought truncate was similar to delete + vacuum full,
> but checking for your scenarios, I did not see an update on
> pg_stat_user_table on truncate for vacuums.
>
> then i checked
> PostgreSQL Internals: TRUNCATE (pykello.github.io)
> 
> to help understand truncation better.
>
> but then i still do not understand how a col type *text* which is dynamic
> results in mem growth (coz there are no rows inserted, i understand for
> long strings db does work to compress, move them to toast tables etc) but
> these are empty rows.
>
> Maybe someone else will be able to explain what is going on.
>
>
>
>
> On Fri, 28 May 2021 at 06:52, Nick Muerdter  wrote:
>
>> I've been seeing what looks like unbounded memory growth (until the OOM
>> killer kicks in and kills the postgres process) when running a pl/pgsql
>> function that performs TRUNCATE statements against various temporary tables
>> in a loop. I think I've been able to come up with some fairly simple
>> reproductions of the issue in isolation, but I'm trying to figure out if
>> this is a memory leak or of I'm perhaps doing something wrong with tuning
>> or other settings.
>>
>> What I've observed:
>>
>> - The memory growth occurs if the temp table has indexes or a primary key
>> set on it.
>> - Alternatively, the memory growth also occurs if the temp table has
>> certain column types on it (eg, "text" types).
>> - If the table doesn't have indexes and only has integer columns present,
>> then the memory growth does *not* occur.
>> - I originally saw this against a PostgreSQL 12 server, but I've tested
>> this against PostgreSQL 9.6.22, 12.7, and 13.3 Docker containers and
>> reproduced it against all versions in the containers.
>>
>> Here are 2 separate examples that seem to show the memory growth on the
>> server (the first being a table with a "text" column, the second example
>> having no text column but a primary key index):
>>
>> DO $$
>>   DECLARE
>> i bigint;
>>   BEGIN
>> CREATE TEMPORARY TABLE pg_temp.foo (id integer, bar text);
>>
>> FOR i IN 1..2 LOOP
>>   TRUNCATE pg_temp.foo;
>> END LOOP;
>>   END
>> $$
>>
>> DO $$
>>   DECLARE
>> i bigint;
>>   BEGIN
>> CREATE TEMPORARY TABLE pg_temp.foo (id integer);
>> ALTER TABLE pg_temp.foo ADD PRIMARY KEY (id);
>>
>> FOR i IN 1..2 LOOP
>>   TRUNCATE pg_temp.foo;
>> END LOOP;
>>   END
>> $$
>>
>> Compare that to this example (which doesn't have an index or any other
>> column types that trigger this), which does *not* show any memory growth:
>>
>> DO $$
>>   DECLARE
>> i bigint;
>>   BEGIN
>> CREATE TEMPORARY TABLE pg_temp.foo (id integer);
>>
>> FOR i IN 1..2 LOOP
>>   TRUNCATE pg_temp.foo;
>> END LOOP;
>>   END
>> $$
>>
>> Any help in determining what's going on here (or if there are other ways
>> to go about this) would be greatly appreciated!
>>
>> Thank you!
>> Nick
>>
>>
>>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>
>