Re: FTS trigger works 1 at a time, but fails with bulk insert script - Mailing list pgsql-general

On 10/9/18 3:25 PM, Malik Rumi wrote:
> I have several different logs. I'm not sure which is the 'right' one. 
> None of them show anything other than routine start and stop activity, 
> even after I added a single Entry document. However, I did notice 
> despite the 'incomplete startup', the last one is the only one that 
> mentions listening on any port. That might be just a version change. I 
> originally installed 9.4. I tried 9.5 but knew it didn't go right. I 
> tried 10 when I was still on Ubuntu 16.04, which is not supposed to be 
> compatible with 10. I have since gone up to Ubuntu 18.04 but have not 
> tried to re-install pg 10. Bottom line, this seems to be a completely 
> different problem.

The current logs will be the ones without the number suffix so:

postgresql-9.4-main.log -- Is the current log

postgresql-9.4-main.log.1 -- Is a past log. The lower the number the 
more recent it was rotated out. So this is the last log before the 
current one.

The information in the logs is controlled by settings in the 
postgresql.conf file:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html

Assuming you are using the Debian/Ubuntu packing of Postgres do:

pg_lsclusters

This will return something like:

Ver Cluster Port Status Owner    Data directory               Log file

9.6 main    5422 down   postgres /var/lib/postgresql/9.6/main 
/var/log/postgresql/postgresql-9.6-main.log

10  main    5432 online postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log


The first two fields above(Ver, Cluster) are the important ones for now. 
Use them to navigate to:

/etc/postgresql/Ver/Cluster

to find the postgresql.conf file for the cluster.

Look at see how your logging is configured and make adjustments using 
the information in the logging conf link above. You will need to reload 
the server to get the changes to take. At the least I would set 
log_connections and log_disconnections to 'on'. This will at least allow 
you to see which server you are connecting to. I would also set 
log_statement to at least 'mod' so you can see your INSERT's.





> 
> 
> malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.4-main.log.1
> <snip>
> 2018-10-07 11:09:53 CDT [1223-1] [unknown]@[unknown] LOG:  incomplete 
> startup packet
> 2018-10-07 11:09:53 CDT [1222-1] LOG:  database system was shut down at 
> 2018-10-06 17:41:15 CDT
> 2018-10-07 11:09:53 CDT [1224-1] postgres@postgres FATAL:  the database 
> system is starting up
> <snip>
> 2018-10-07 11:09:54 CDT [1149-1] LOG:  database system is ready to 
> accept connections
> 2018-10-07 was two days ago!
> 
> malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.4-main.log
> <snip>
> 2018-10-08 22:22:17 CDT [1266-2] LOG:  database system is shut down
> 2018-10-09 07:59:49 CDT [1216-1] LOG:  database system was shut down at 
> 2018-10-08 22:22:17 CDT
> 2018-10-09 07:59:49 CDT [1217-1] [unknown]@[unknown] LOG:  incomplete 
> startup packet
> 2018-10-09 07:59:50 CDT [1227-1] postgres@postgres FATAL:  the database 
> system is starting up
> <snip>
> 2018-10-09 07:59:51 CDT [1154-1] LOG:  database system is ready to 
> accept connections
> What's with the fatal and the incomplete startup? Given that, how ready 
> is it really to accept connections?
> 
> malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.5-main.log
> <snip>
> 2018-10-08 22:22:18 CDT [1271-2] LOG:  database system is shut down
> 2018-10-09 07:59:49 CDT [1210-1] LOG:  database system was shut down at 
> 2018-10-08 22:22:18 CDT
> 2018-10-09 07:59:49 CDT [1211-1] [unknown]@[unknown] LOG:  incomplete 
> startup packet
> 2018-10-09 07:59:50 CDT [1226-1] postgres@postgres FATAL:  the database 
> system is starting up
> <snip>
> 2018-10-09 07:59:50 CDT [1153-1] LOG:  database system is ready to 
> accept connections
> Same question / observation
> 
> malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-9.5-main.log.1
> <snip>
> 2018-10-07 11:09:53 CDT [1220-1] LOG:  database system was shut down at 
> 2018-10-06 17:41:15 CDT
> 2018-10-07 11:09:53 CDT [1221-1] [unknown]@[unknown] LOG:  incomplete 
> startup packet
> 2018-10-07 11:09:53 CDT [1225-1] postgres@postgres FATAL:  the database 
> system is starting up
> <snip>
> 2018-10-07 11:09:54 CDT [1150-1] LOG:  database system is ready to 
> accept connections
> Same - and this was two days ago, too
> 
> malikarumi@Tetuoan2:/var/log/postgresql$ tail -f postgresql-10-main.log
> 2018-10-08 22:22:16.047 CDT [1159] LOG:  received fast shutdown request
> 2018-10-08 22:22:17.337 CDT [1159] LOG:  aborting any active transactions
> 2018-10-08 22:22:17.927 CDT [1159] LOG:  worker process: logical 
> replication launcher (PID 1281) exited with exit code 1
> 2018-10-08 22:22:17.988 CDT [1276] LOG:  shutting down
> 2018-10-08 22:22:19.327 CDT [1159] LOG:  database system is shut down
> 2018-10-09 07:59:48.574 CDT [1155] LOG:  listening on IPv4 address 
> "127.0.0.1", port 5434
> 2018-10-09 07:59:48.727 CDT [1155] LOG:  listening on Unix socket 
> "/var/run/postgresql/.s.PGSQL.5434"
> 2018-10-09 07:59:50.590 CDT [1223] LOG:  database system was shut down 
> at 2018-10-08 22:22:19 CDT
> 2018-10-09 07:59:51.058 CDT [1155] LOG:  database system is ready to 
> accept connections
> 2018-10-09 07:59:51.617 CDT [1274] [unknown]@[unknown] LOG:  incomplete 
> startup packet
> 
> 
> */“None of you has faith until he loves for his brother or his neighbor 
> what he loves for himself.”/*
> 
> 
> On Mon, Oct 8, 2018 at 6:36 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 10/8/18 3:54 PM, Adrian Klaver wrote:
>      > On 10/8/18 1:58 PM, Malik Rumi wrote:
>      >> So what is the script you used to do the bulk INSERT?
>      >>
>      >> There's actually three, but they are all basically the same. The
>      >> differences have to do with the source material being inserted:
>      >>
>      >> # usr/local/bin/python3.6
>      >> # coding: utf-8
>      >>
>      >> from os import environ
>      >> environ['DJANGO_SETTINGS_MODULE'] = 'chronicle.settings'
>      >> import django
>      >> django.setup()
>      >> from ktab.models import Entry, Tag
>      >
>      > So I am going to assume Entry and Tag map to the tables
>     ktab_entry and
>      > public.ktab_entry_tags respectively.
>      >
>      >> from django.utils.text import slugify
>      >> import csv
>      >>
>      >>
>      >> filename =
>     '/home/malikarumi/Projects/Progress_Logs/edit_blogpost.csv'
>      >>
>      >> with open(filename, 'rt') as text:
>      >>      reader = csv.DictReader(text, delimiter=',')
>      >>      # next(reader, None)
>      >>      for row in reader:
>      >>          my_entry = Entry.objects.create(
>      >>              title=row['title'], slug=row['slug'],
>      >> chron_date=row['created'],
>      >>              clock=row['clock'], content=row['content'])
>      >>          my_entry.tags.add(row['tag'])
>      >
>      > I thought that you needed to pass a model object to add(). If I am
>      > following the above it is just a csv field value.
>      >
>      > Assuming log_statement = 'mod', I would tail the postgresql.conf
>     file to
>      > see what is actually hitting the database.
> 
>     Aargh, meant tail the Postgres log file.
> 
>      >
>      >
>      >
>      >> */“None of you has faith until he loves for his brother or his
>      >> neighbor what he loves for himself.”/*
>      >>
>      >>
>      >
>      >
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com