Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI
Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free
and readily available?
It also has a graphical table-from-file loader as well.
On Fri, 7 Dec 2018 at 23:35, Adrian Klaver
wrote:
> On 12/7/18 9:04 AM, s4...@yahoo.co.jp wrote:
> > I didn't specify any schema, so it was created in public schema.
> > The error message also says "public"...
> > //--
> > ERROR: column "rec_id" of relation "spec" does not exist
> > LINE 1: INSERT INTO "public"."spec" ("rec_id","title_c...
> > //--
> >
> > Output of the \d spec:
> >
> >
> > Table "public.spec"
> > Column | Type | Modifiers
> > +-+---
> > rec_id | character varying(32) | not null
> > title_category | character varying(255) |
> > doctype| character varying(255) |
> > ... goes on like this for other columns.
> >
> > What are you trying to see in the output of \d spec?
>
> My basic procedure in troubleshooting is starting from the known and
> working out to the unknown. So my questions about the schema(s) and the
> table definition where to establish a know starting point. Also a common
> issue that hit this list are multiple versions(across schemas) of an
> object in a database and code hitting the wrong version. One of the
> signs of that being error messages of the form you got.
>
>
> >
> > I don't understand what you mean by the import code is trying to insert
> > in to wrong version of the table.
> > I visually checked the left side "menu like" structure of the
> > phpPgAdmin- there is no other table of that name.
>
> See above.
>
> >
> > You mentioned that quoted identifiers are not the issue.
> > This prompted me to test the process in a table with a few columns and
> > ascii characters.
> > Immediately it was clear that quoted identifiers were not to blame.
> >
> > I found that I got that error when I change encoding of the tab
> > delimited file to UTF-8.
> > Because my data contains non-ascii characters, if I don't use UTF-8, I
> > get this error.
> >
> > ERROR: invalid byte sequence for encoding "UTF8": 0x82
> >
> >
> > ... and I read somewhere that if I open the text file in notpad and save
> > it with UTF-8 encoding, I can get rid of the error. (When inserting
> > using pyDev (psycopg2)/Eclipse, that does get rid of the error...
>
> Notepad is not a text editor to use in general and in particular for
> data transformation work. It has limited knowledge of the text format.
> If you need to do that on Windows use Wordpad or better yet Notepad++:
>
> https://notepad-plus-plus.org/
>
> >
> > That's why I changed encoding.
> >
> > And now I am stuck with this error.
> >
> > But at least, now I am not blaming phpPgAdmin :)
> > Thanks for the lead.
> >
> > BTW, both server and client encoding of my pg db are UTF8.
>
> The original encoding was Win-10 (Japanese) correct?
>
> >
> > testdb=# SHOW SERVER_ENCODING;
> > server_encoding
> > -
> > UTF8
> > (1 row)
> >
> > testdb=# SHOW CLIENT_ENCODING;
> > client_encoding
> > -
> > UTF8
> > (1 row)
> >
> > testdb=#
> >
> >
> > - Original Message -
> > *From:* Adrian Klaver
> > *To:* s4...@yahoo.co.jp; rob stone ;
> > "pgsql-general@lists.postgresql.org"
> >
> > *Date:* 2018/12/7, Fri 23:47
> > *Subject:* Re: Importing tab delimited text file using phpPgAdmin
> > 5.1 GUI
> >
> > On 12/7/18 12:28 AM, s4...@yahoo.co.jp
> wrote:
> > > Hello Adrian, Rob!
> > >
> > > Thank you for the comments.
> > >
> > > Oh, yes, I forgot to mention that I am using Postgresql version
> 9.6.
> > > I had read somewhere that last supported version was 9.3 or
> > something,
> > > could be 9.2 as you say.
> > >
> > > I wanted to use phpPgAdmin, that's why I went back to 9.6 even if
> > I had
> > > installed ver. 10 first.
> > > But if the phpPgAdmin quotes identifiers by defaults, I will need
> to
> > > look for some other ways.
> >
> > I don't think the quoted identifiers are the issue. I am suspecting
> > that
> > the import code may be trying to INSERT into the wrong version of the
> > table. Some questions:
> >
> > 1) Which schema did you create spec in?
> >
> > 2) In psql what does \d spec show?
> >
> >
> > >
> > > Strictly speaking, I don't need to use the phpPgAdmin, but I am
> > trying
> > > to find a GUI way to upload a file quickly (the file has
> > thousands of
> > > records.)
> > >
> > > Now, I am using pyDev in Eclipse to insert records, but I need to
> > use a
> > > web-based click and upload.
> >
> > ?
> > https://www.pgadmin.org/
> >
> >
Tables(s) that feed pg_controldata
In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what tables do I query to get these values, and can I also get them from the streamed replication host? Database cluster state Latest checkpoint location Time of latest checkpoint [postgres@fpslbxhaprl05 ~]$ pg_controldata pg_control version number: 922 Catalog version number: 201204301 Database system identifier: 6114483497489611387 Database cluster state: in production pg_control last modified: Sat 08 Dec 2018 11:08:56 AM EST Latest checkpoint location: C50/87A8F300 Prior checkpoint location: C50/85506608 Latest checkpoint's REDO location: C50/85B86620 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/1965571123 Latest checkpoint's NextOID: 158912675 Latest checkpoint's NextMultiXactId: 18962475 Latest checkpoint's NextMultiOffset: 38526531 Latest checkpoint's oldestXID: 1769771528 Latest checkpoint's oldestXID's DB: 16384 Latest checkpoint's oldestActiveXID: 1965571123 Time of latest checkpoint: Sat 08 Dec 2018 10:56:56 AM EST Minimum recovery ending location: 0/0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no Current wal_level setting: hot_standby Current max_connections setting: 250 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 320 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value -- Angular momentum makes the world go 'round.
pg_ctl kill INT vs. pg_cancel_backend
Hi, Is there a substantive difference between the two, or are they just different interfaces to the same action? -- Angular momentum makes the world go 'round.
amazon aroura config - seriously overcommited defaults? (May be Off Topic)
All;
My apologies if this is off topic.
Our company is moving to Aurora, In the past I would take care not to
allow postgresql to over-commit memory beyond the actual memory on the
server, which meant I would add the buffer pool + (work_mem *
max_connections) + (maintenance_work_mem * autovacuum threads)
However as I look at the aroura defaults they are all off the charts,
for example, based on the calculations in the config (amazon doesn't
make it easy, some settings are in pages, some are in kb, some are who
knows what) I see the following settings as default in our aroura config:
The instance size is db.r4.xlarge
this instance size is listed as having 30.5GB of ram
Here's the default settings:
shared_buffers: {DBInstanceClassMemory/10922}
which equates to 24GB
work_mem: 64000 (kb)
which equates to 65.5MB
maintenance_work_mem: GREATEST({DBInstanceClassMemory/63963136*1024},65536)
which equates to 4.2GB
max_connections: LEAST({DBInstanceClassMemory/9531392},5000)
which equates to 3,380
According to my math (If I got it right) in a worst case scenario,
if we maxed out max_connections, work_mem and maintenance_work_mem limits
the db would request 247GB of memory
Additionally amazon has set effective_cache_size =
{DBInstanceClassMemory/10922}
which equates to about 2.9MB (which given the other outlandish setting
may be the only appropriate setting in the system)
What the hell is amazon doing here? Am I missing the boat on tuning
postgresql memory? Is amazon simply counting on the bet that users will
never fully utilize an instance?
Thanks in advance
Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
so 8. 12. 2018 v 20:04 odesílatel Square Bob napsal:
> All;
>
>
> My apologies if this is off topic.
>
>
> Our company is moving to Aurora, In the past I would take care not to
> allow postgresql to over-commit memory beyond the actual memory on the
> server, which meant I would add the buffer pool + (work_mem *
> max_connections) + (maintenance_work_mem * autovacuum threads)
>
>
> However as I look at the aroura defaults they are all off the charts,
> for example, based on the calculations in the config (amazon doesn't
> make it easy, some settings are in pages, some are in kb, some are who
> knows what) I see the following settings as default in our aroura config:
>
>
> The instance size is db.r4.xlarge
>
>
> this instance size is listed as having 30.5GB of ram
>
>
> Here's the default settings:
>
>
> shared_buffers: {DBInstanceClassMemory/10922}
>
> which equates to 24GB
>
>
> work_mem: 64000 (kb)
>
> which equates to 65.5MB
>
>
> maintenance_work_mem: GREATEST({DBInstanceClassMemory/63963136*1024},65536)
>
> which equates to 4.2GB
>
>
> max_connections: LEAST({DBInstanceClassMemory/9531392},5000)
>
> which equates to 3,380
>
>
> According to my math (If I got it right) in a worst case scenario,
>
> if we maxed out max_connections, work_mem and maintenance_work_mem limits
>
> the db would request 247GB of memory
>
>
> Additionally amazon has set effective_cache_size =
> {DBInstanceClassMemory/10922}
>
> which equates to about 2.9MB (which given the other outlandish setting
> may be the only appropriate setting in the system)
>
>
>
> What the hell is amazon doing here? Am I missing the boat on tuning
> postgresql memory? Is amazon simply counting on the bet that users will
> never fully utilize an instance?
>
>
nobody knows what patches are used there. Max connections over 1000 are
not good idea for native Postgres. But maybe there are some patches - or
just mostly idle connections are expected.
Regards
Pavel
> Thanks in advance
>
>
>
>
>
Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: > On RDS PostgreSQL, the default is 25% of your server memory. This seems > to be pretty widely accepted as a good starting point on PostgreSQL. FWIW, I think it's widely cited, but also bad advice. 25% for a OLTP workload on a 1TB machine with a database size above 25% is a terrible idea.
Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
> On Dec 8, 2018, at 3:12 PM, Andres Freund wrote: > > On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: >> On RDS PostgreSQL, the default is 25% of your server memory. This seems >> to be pretty widely accepted as a good starting point on PostgreSQL. > > FWIW, I think it's widely cited, but also bad advice. 25% for a OLTP > workload on a 1TB machine with a database size above 25% is a terrible > idea. > Sorry, could you please expand “database size above 25%”? 25% of what? rjs
Re: Transaction Id Space, Freezing and Wraparound
On 11/20/18 13:00, Tom Lane wrote: > If the stored XIDs were 64 bits wide, we'd not have to bother with all > of this mess ... but adding another 64 bits to tuple headers would be > a painful space cost, not to mention the storage compatibility issues. People keep saying that. But didn't someone come up with a way to do this by storing the top 32 bits just once somewhere in the block, rather than adding 64 bits to every tuple? I can't remember if there was an email thread. Maybe some other drawbacks to that approach, but lets at least point out adding 64 bits to the tuple header isn't the only way to accomplish this. And the other thread about memory management... if I'm going to start religious wars, might as well just start them all at once right? ;) -J -- http://about.me/jeremy_schneider
Errors with schema migration and logical replication — expected?
Hi, first time poster.
I just ran into a rather messy problem when doing a schema migration with
logical replication. I'm not entirely sure what went wrong, why, or how to
prevent it in the future. The migration I ran was pretty simple (though
auto-generated by Django):
BEGIN;ALTER TABLE "search_docketentry" ADD COLUMN
"pacer_sequence_number" smallint NULL;ALTER TABLE "search_docketentry"
ALTER COLUMN "pacer_sequence_number" DROP DEFAULT;ALTER TABLE
"search_docketentry" ADD COLUMN "recap_sequence_number" varchar(50)
DEFAULT '' NOT NULL;ALTER TABLE "search_docketentry" ALTER COLUMN
"recap_sequence_number" DROP DEFAULT;ALTER TABLE "search_docketentry"
ALTER COLUMN "entry_number" DROP NOT NULL;ALTER TABLE
"search_recapdocument" ALTER COLUMN "document_number" SET DEFAULT
'';ALTER TABLE "search_recapdocument" ALTER COLUMN "document_number"
DROP DEFAULT;ALTER TABLE "search_docketentry" DROP CONSTRAINT
"search_docketentry_docket_id_12fd448b9aa007ca_uniq";CREATE INDEX
"search_docketentry_recap_sequence_number_1c82e51988e2d89f_idx" ON
"search_docketentry" ("recap_sequence_number", "entry_number");CREATE
INDEX "search_docketentry_eb19fcf7" ON "search_docketentry"
("pacer_sequence_number");CREATE INDEX "search_docketentry_bff4d47b"
ON "search_docketentry" ("recap_sequence_number");CREATE INDEX
"search_docketentry_recap_sequence_number_d700f0391e8213a_like" ON
"search_docketentry" ("recap_sequence_number" varchar_pattern_ops);
COMMIT;
BEGIN;ALTER TABLE "search_docketentry"
ALTER COLUMN "pacer_sequence_number" TYPE integer;
COMMIT;
And after running this migration, I started getting this error on the
subscriber:
2018-12-09 05:59:45 UTC::@:[13373]:LOG: logical replication apply
worker for subscription "replicasubscription" has started
2018-12-09 05:59:45 UTC::@:[13373]:ERROR: null value in column
"recap_sequence_number" violates not-null constraint
2018-12-09 05:59:45 UTC::@:[13373]:DETAIL: Failing row contains
(48064261, 2018-12-07 04:48:40.388377+00, 2018-12-07
04:48:40.388402+00, null, 576, , 4571214, null, null).
2018-12-09 05:59:45 UTC::@:[6342]:LOG: worker process: logical
replication worker for subscription 18390 (PID 13373) exited with exit
code 1
So, my migration created a new column with a null constraint and somehow
the subscriber got data that violated that. I don't know how that's
possible since this was a new column and it was never nullable.
I applied the above migration simultaneously on my publisher and subscriber
thinking that postgresql was smart enough to do the right thing. I think
the subscriber finished first (it has less traffic).
The docs hint that postgresql might be smart enough to not worry about the
order you do migrations:
> *Logical replication is robust when schema definitions change in a live
database:* When the schema is changed on the publisher and replicated data
starts arriving at the subscriber but does not fit into the table schema,
replication will error until the schema is updated.
And it even hints that doing a migration on the subscriber first is a good
thing in some cases:
> In many cases, intermittent errors can be avoided by applying additive
schema changes to the subscriber first.
But I'm now supremely skeptical that doing anything at the subscriber first
is a good idea. Are the docs wrong? Does the above error make sense? Is the
process for schema migrations documented somewhere beyond the above?
I have lots of questions because I thought this would have gone smoother
than it did.
As for the fix: I made the column nullable on the subscriber and I'm
waiting for it to catch up. Once it does I'll re-sync its schema with the
publisher. Anybody interested in following along with all this (or finding
this later and having questions) can follow the issue here:
https://github.com/freelawproject/courtlistener/issues/919
Thank you for the lovely database! I hope this is helpful.
Mike
Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
On 2018-12-08 15:23:19 -0800, Rob Sargent wrote: > > > > On Dec 8, 2018, at 3:12 PM, Andres Freund wrote: > > > > On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: > >> On RDS PostgreSQL, the default is 25% of your server memory. This seems > >> to be pretty widely accepted as a good starting point on PostgreSQL. > > > > FWIW, I think it's widely cited, but also bad advice. 25% for a OLTP > > workload on a 1TB machine with a database size above 25% is a terrible > > idea. > > > > Sorry, could you please expand “database size above 25%”? 25% of what? Memory available to postgres (i.e. 100% of the server's memory on a server dedicated to postgres, less if it's shared duty). Greetings, Andres Freund
