Binary downloads and upgrading the host OS

[Tom Browder]

I am using the PostgreSQL binary downloads for Debian from your apt
repositories.

I am about to upgrade from Debian 9 to Debian 10 and would like
comments for my plan:

1. I will follow Debian's instructions for upgrading from 9 to 10.

2. Before I start the actual upgrade, I will:

+ ensure all is up-to-date for my PostgreSQL as well as the rest of my system
+ temporarily comment out the PostgreSQL links in /etc/apt.
+ stop the pgsql server and prevent auto-restart upon reboot

3. After the successful upgrade I assume I will:

+ update the apt sources for PostgreSQL with the new name (buster)
+ update as usual
+ restart the pgsql server and resume auto-start upon reboot

Thanks for your help.

Blessings,

-Tom

Re: Binary downloads and upgrading the host OS

[Adrian Klaver]

On 4/25/20 8:04 AM, Tom Browder wrote:

I am using the PostgreSQL binary downloads for Debian from your apt
repositories.

I am about to upgrade from Debian 9 to Debian 10 and would like
comments for my plan:

1. I will follow Debian's instructions for upgrading from 9 to 10.

2. Before I start the actual upgrade, I will:

+ ensure all is up-to-date for my PostgreSQL as well as the rest of my system
+ temporarily comment out the PostgreSQL links in /etc/apt.
+ stop the pgsql server and prevent auto-restart upon reboot

3. After the successful upgrade I assume I will:

+ update the apt sources for PostgreSQL with the new name (buster)
+ update as usual
+ restart the pgsql server and resume auto-start upon reboot


Looks good to me. I'm assuming you are leaving the Postgres version the 
same though. Is that correct?


Also, if it is not already planned, to be on the safe side throw in a 
backup of some sort just before shutting down the server.




Thanks for your help.

Blessings,

-Tom





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Binary downloads and upgrading the host OS

[Tom Browder]

On Sat, Apr 25, 2020 at 10:24 AM Adrian Klaver
 wrote:
> On 4/25/20 8:04 AM, Tom Browder wrote:
> > I am using the PostgreSQL binary downloads for Debian from your apt
> > repositories.
...
> Looks good to me. I'm assuming you are leaving the Postgres version the
> same though. Is that correct?

Yes, I already use the latest stable via the apt setup (12 I think).

> Also, if it is not already planned, to be on the safe side throw in a
> backup of some sort just before shutting down the server.

Yes indeed!

Thanks, Adrian.

-Tom

Re: Triggers and Full Text Search *

[Malik Rumi]

@Ericson
I have the script (statements?) by which I created the triggers, but since
you asked I do not see them in pga4, from which I manage my postgres. I
don't know if this is significant or not. Also, this was originally done
quite a while ago, so my memory may be fuzzy. From the text of the
statement "runSQL..." I think I ran this in the terminal. So this is the
closest thing I can find to your request. The text that I previously posted
can be found in 'triggers functions' under this schema in pga4, but not
these statements. Also, further up the pga4 tree, "event triggers" is
blank. I mention these things because I am not sure of their importance.
Thanks.

triggers.py

# Trigger on insert or update of ktab.Entry

migrations.RunSQL('''CREATE OR REPLACE FUNCTION
entry_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  SELECT setweight(to_tsvector(NEW.title), 'A') ||
 setweight(to_tsvector(NEW.content), 'B') ||
 setweight(to_tsvector(NEW.category), 'D') ||
 setweight(to_tsvector(COALESCE(string_agg(tags.tag, ', '), '')),
'C')
  INTO NEW.search_vector
  FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON entry_tags.entry_id =
entry.id
LEFT JOIN ktab_tags AS tag ON tag.id = entry_tags.tag_id
  WHERE entry.id = NEW.id
  GROUP BY entry.id, category;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update BEFORE INSERT OR UPDATE ON ktab_entry
  FOR EACH ROW EXECUTE PROCEDURE entry_search_vector_trigger();''')

# Trigger after ktab.Author is updated
'''
Since I don't have author, and besides, his author was a separate table -
SKIP
CREATE OR REPLACE FUNCTION author_search_vector_trigger() RETURNS trigger
AS $$
BEGIN
  UPDATE ktab_entry SET id = id WHERE author_id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE ON ktab_author
  FOR EACH ROW EXECUTE PROCEDURE author_search_vector_trigger();
'''

# Trigger after ktab.Entry.tags are added, deleted from a entry
migrations.RunSQL('''CREATE OR REPLACE FUNCTION
tags_search_vector_trigger() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
  ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE OR DELETE ON
ktab_entry_tags
  FOR EACH ROW EXECUTE PROCEDURE tags_search_vector_trigger();
''')

# Trigger after ktab.Tag is updated
migrations.RunSQL('''CREATE OR REPLACE FUNCTION tag_search_vector_trigger()
RETURNS trigger AS $$
BEGIN
  UPDATE ktab_entry SET id = id WHERE id IN (
SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER UPDATE ON ktab_tag
  FOR EACH ROW EXECUTE PROCEDURE tag_search_vector_trigger();


*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*


On Tue, Apr 21, 2020 at 1:25 PM Ericson Smith  wrote:

> My apologies - I did not look closely at the manual. Many many years ago
> (6.xx days I had a similar problem and leapt to answer).
>
> Could you post your CREATE TRIGGER statements as well?
>
>
> On Wed, Apr 22, 2020 at 1:21 AM Malik Rumi  wrote:
>
>> @Ericson,
>> Forgive me for seeming dense, but how does COPY help or hurt here?
>>
>> @Andreas,
>> I had to laugh at your reference to "prose". Would you believe I am
>> actually a published playwright? Long before I started coding, of course.
>> Old habits die hard.
>>
>> entry_search_vector_trigger
>> BEGIN
>>   SELECT setweight(to_tsvector(NEW.title), 'A') ||
>>  setweight(to_tsvector(NEW.content), 'B') ||
>>  setweight(to_tsvector(NEW.category), 'D') ||
>>  setweight(to_tsvector(COALESCE(string_agg(tag.tag,
>> ', '), '')), 'C')
>>   INTO NEW.search_vector
>>   FROM ktab_entry AS entry
>> LEFT JOIN ktab_entry_tags AS entry_tags ON
>> entry_tags.entry_id = entry.id
>> LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
>>   WHERE entry.id = NEW.id
>>   GROUP BY entry.id, category;
>>   RETURN NEW;
>> END;
>>
>> tag_search_vector_trigger
>> BEGIN
>>   UPDATE ktab_entry SET id = id WHERE id IN (
>> SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
>>   );
>>   RETURN NEW;
>> END;
>>
>> tags_search_vector_trigger
>> BEGIN
>>   IF (TG_OP = 'DELETE') THEN
>> UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
>> RETURN OLD;
>>   ELSE
>> UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
>> RETURN NEW;
>>   END IF;
>> END;
>>
>> search_vecto

Re: Binary downloads and upgrading the host OS

[Magnus Hagander]

On Sat, Apr 25, 2020 at 5:39 PM Tom Browder  wrote:

> On Sat, Apr 25, 2020 at 10:24 AM Adrian Klaver
>  wrote:
> > On 4/25/20 8:04 AM, Tom Browder wrote:
> > > I am using the PostgreSQL binary downloads for Debian from your apt
> > > repositories.
> ...
> > Looks good to me. I'm assuming you are leaving the Postgres version the
> > same though. Is that correct?
>
> Yes, I already use the latest stable via the apt setup (12 I think).
>
> > Also, if it is not already planned, to be on the safe side throw in a
> > backup of some sort just before shutting down the server.
>
> Yes indeed!
>

Also don't forget that on an upgrade from Debian 9 to 10, you most likely
need to reindex your string indexes, see
https://wiki.postgresql.org/wiki/Locale_data_changes
.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Binary downloads and upgrading the host OS

[Tom Browder]

On Sat, Apr 25, 2020 at 17:07 Magnus Hagander  wrote:
...

> Also don't forget that on an upgrade from Debian 9 to 10, you most likely
> need to reindex your string indexes, see
> https://wiki.postgresql.org/wiki/Locale_data_changes
> .
>

Thanks, Magnus!

-Tom