Re: [GENERAL] cluster question

[Alex Samad]

Hi

Quick question.  I have a 2 node cluster - each node has its own ip.

But from reading this, I really need a 3rd ip, which potentially floats
between the nodes to which ever is the master / rw node.

Is that right? Sort of makes sense I guess

A

On 14 August 2017 at 16:47, Andreas Kretschmer 
wrote:

> On 14 August 2017 08:39:54 GMT+02:00, Alex Samad 
> wrote:
> >Hi
> >
> >I have setup a streaming replicating cluster, with a hot standby.
> >
> >Now I would like to change the RW to hot standby and change the hot
> >standby
> >to be the RW server.
> >
> >Is it just a matter of updating recover.conf file ?
> >
> >Alex
>
> I would suggest you repmgr, with this tool you can do "repmgr standby
> switchover" to perform such tasks.
>
> Regards, Andreas.
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

Re: [GENERAL] WAL replication wrong collate

[Michael Paquier]

On Tue, Aug 15, 2017 at 3:52 AM, basti  wrote:
> master and slave had set the same locales.
> I dont unterstand that i can create a database in en_us.utf8 and then when i
> did the basebackup it's change to c locale.
> I cant find any option for pg_basebackup to set locale/collate.
> I use this howto https://wiki.postgresql.org/wiki/Streaming_Replication and
> had install an other cluster in the past with successful.
>
> How can help please?

Are you sure that you are connecting to the right standby server? The
creation of a database is WAL-logged in roughly two phases:
- Log the new pg_database record.
- Copy the directory of the template database to the new database.
So collations are conserved at replay.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Queries for Diagramming Schema Keys

[Melvin Davidson]

On Mon, Aug 14, 2017 at 6:38 PM,  wrote:

> ...
> > Just to add that running psql with the -E switch is REALLY handy for
> seeing how psql executes queries to
> > find how tables etc are put together.
>
> I can't actually use that feature to gather the information I'm interested
> in since all I have are tables and data with no formal information on key
> relations and allowed load order. There is so much data in so many tables
> that testing even a single load iteration takes many hours and there are
> literally many thousands of load order combinations possible. Logs of
> hundreds of thousands (or millions) of XML loads would take a very long
> time to go through, and would then only give one possible load order.
>
> Automating a diagram of key relations and visualizing it is the first step
> to methodically computing a correct load order, but I can't do that until I
> figure out how to use the system tables to describe (1) columns which are
> not keys, (2) columns which are primary keys not referring to another
> column, and (3) columns which are foreign keys and the table/column they
> are pointed at. My SQL knowledge is somewhat limited and I am struggling
> with the system tables.
>


*For *
*> (3) columns which are foreign keys and the table/column they are pointed
a*

*This should do the trick, you can tweak as needed.*














*SELECT nsp.nspname,   rel.relname,   con.conname,
con.contype,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
JOIN pg_namespace nsp ON (nsp.oid = rel.relnamespace)  JOIN pg_constraint
con ON (con.conrelid = rel.oid) WHERE contype = 'f'   AND rel.relname =
'account'  ORDER by relname,   contype,   conname;*
*However, for the others, I have no intention of creating the queries for
you. I encourage you to learn the PostgreSQL system catalogs.*
*You have not provided us with the version of PostgreSQL you are using, so
I'll just point you to the relevant part in the latest doc.*


*https://www.postgresql.org/docs/9.6/static/catalogs.html
*


*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...

> Just to add that running psql with the -E switch is REALLY handy for seeing 
> how psql executes queries to
> find how tables etc are put together.

 
I can't actually use that feature to gather the information I'm interested in 
since all I have are tables and data with no formal information on key 
relations and allowed load order. There is so much data in so many tables that 
testing even a single load iteration takes many hours and there are literally 
many thousands of load order combinations possible. Logs of hundreds of 
thousands (or millions) of XML loads would take a very long time to go through, 
and would then only give one possible load order.
 
Automating a diagram of key relations and visualizing it is the first step to 
methodically computing a correct load order, but I can't do that until I figure 
out how to use the system tables to describe (1) columns which are not keys, 
(2) columns which are primary keys not referring to another column, and (3) 
columns which are foreign keys and the table/column they are pointed at. My SQL 
knowledge is somewhat limited and I am struggling with the system tables.

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...snip...
For all tables and columns I get no output at all. Basically I need all tables 
and columns for which the column is neither a regular primary key, nor a 
foreign key. This will be marked as an attribute in the diagramming program.
 
Your query for primary keys which are not foreign keys seems to be magic :) 
I'll have to dig through some of the tables (nearly 1000 of them) to validate, 
but it seems to work with much less complication than my original query (and I 
think my original had issues...I forgot to mention that I had been relying on 
the columns which implemented values which foreign keys had referenced were 
assumed to have the same column names...mostly this had been true, but not 
always, so my original approach was a guaranteed failure).
 
For foreign keys I need a row with both the table and column which is a foreign 
key, and the specific table and column it maps to. My mapping program will be 
adding a double-linked list among keys for validation and for interactive use 
of an SVG image being produced.
 
So I'm still looking for 
"original_table.fk_column->implementing_table.implementing_column", plus the 
list of "table.column" where "column" is not a key.
 

>I am completely at a loss how I would query for all columns

Does this help?
 
-- TABLES AND COLUMNS
SELECT c.table_schema as schema,   c.table_name as table,
c.ordinal_position as order,   c.column_name as column,   CASE WHEN 
c.data_type IN ('character', 'varchar') THEN c.data_type || '(' || 
c.character_maximum_length || ')'WHEN TRIM(c.data_type) IN 
('numeric')THEN c.data_type || '(' || c.numeric_precision_radix || ',' 
  ||  c.numeric_scale || ')'   ELSE c.data_typeEND,   
c.is_nullable as null,   col_description(t.oid, c.ordinal_position) as 
comment  FROM information_schema.columns c  JOIN pg_class t ON (t.relname = 
c.table_name) WHERE table_schema = 'public'   AND c.table_name = 'album'ORDER 
BY 1, 2, 3;

-- TABLES AND PKEYS
SELECT n.nspname,   t.relname as table,c.conname as pk_name  FROM 
pg_class t  JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'p')  
JOIN pg_namespace n  ON (n.oid = t.relnamespace) WHERE relkind = 'r'   AND 
t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE 'sql_%'   ORDER BY 
n.nspname, t.relname, c.conname;
 
-- TABLES and FKEYS
SELECT n.nspname as schema,t.relname as table,c.conname as 
fk_name  FROM pg_class t  JOIN pg_namespace n ON n.oid = t.relnamespace  JOIN 
pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE relkind = 
'r'   AND t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE 'sql_%'   ORDER BY 
n.nspname, t.relname, c.conname;




Melvin DavidsonI reserve the right to fantasize.  Whether or not you wish to 
share my fantasy is entirely up to you. 

Re: [GENERAL] Queries for Diagramming Schema Keys

[Scott Marlowe]

Just to add that running psql with the -E switch is REALLY handy for seeing
how psql executes queries to find how tables etc are put together.

Re: [GENERAL] Queries for Diagramming Schema Keys

[Melvin Davidson]

On Mon, Aug 14, 2017 at 2:46 PM,  wrote:

> Hi,
>
> I'm trying to write an application (using libpqxx/C++) which creates
> graphical images of large and complex relations between tables (basically
> an SVG image with hot spots for drilling down on details). I need to
> essentially create icons of tables and their columns, and then draw
> interconnecting lines where foreign keys are involved, and to distinctly
> label primary keys, foreign keys, and non-key columns. Eventually this will
> have knowledge of an XML file loading scheme and be able to reverse
> engineer the required load order (imagine approximately 1,000 tables with
> many foreign keys and file loads which may take hours for each failure to
> load). I need some advice on using ANSI/information_schema queries to
> accomplish this.
>
> Thus I have this query to list all tables:
>
> SELECT table_name
>   FROM information_schema.tables
> WHERE table_schema='public'
>AND table_type='BASE TABLE';
>
>
> ...this seems to work ok. The next query is to find all foreign
> keys...this seems to sort of work, but may have issues:
>
> SELECT
> tc.table_name AS local_table,
> kcu.column_name AS key_column,
> ccu.table_name AS fk_table,
> ccu.column_name AS fk_column
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
>   ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
>   ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
> IN (SELECT table_name
>   FROM information_schema.tables
> WHERE table_schema='public'
>AND table_type='BASE TABLE');
>
>
> This is my query to find all primary keys which are not foreign keys, and
> this definitely is not 100% correct:
> SELECT DISTINCT
> tc.table_name AS local_table,
> kcu.column_name AS key_column
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
>   ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
>   ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name
> IN (SELECT table_name
>   FROM information_schema.tables
>  WHERE table_schema='public'
>AND table_type='BASE TABLE')
> AND (tc.table_name, kcu.column_name)
> NOT IN (
> SELECT
> tc.table_name,
> kcu.column_name
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
>   ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
>   ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
> IN (SELECT table_name
>   FROM information_schema.tables
>  WHERE table_schema='public'
>AND table_type='BASE TABLE')
> )
> ORDER BY local_table, key_column;
>
> I am completely at a loss how I would query for all columns which are
> neither primary nor foreign keys. Would anyone have a suggestion for
> something like this:
> SELECT table_name, non_key_column
> FROM ...
> WHERE ...
> ORDER BY table_name, non_key_column
>
> Any advice on any of the queries would be appreciated!
>
> Thanks!
>

>I am completely at a loss how I would query for all columns

*Does this help?*


*-- TABLES AND COLUMNS*






*SELECT c.table_schema as schema,   c.table_name as table,
c.ordinal_position as order,   c.column_name as column,   CASE WHEN
c.data_type IN ('character', 'varchar') THEN c.data_type || '(' ||
c.character_maximum_length || ')'WHEN TRIM(c.data_type) IN
('numeric')THEN c.data_type || '(' || c.numeric_precision_radix ||
',' *










*  ||  c.numeric_scale || ')'   ELSE c.data_type
END,   c.is_nullable as null,   col_description(t.oid,
c.ordinal_position) as comment  FROM information_schema.columns c  JOIN
pg_class t ON (t.relname = c.table_name) WHERE table_schema = 'public'
AND c.table_name = 'album'ORDER BY 1, 2, 3;*

*-- TABLES AND PKEYS*










*SELECT n.nspname,   t.relname as table,c.conname as pk_name
FROM pg_class t  JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype
= 'p')  JOIN pg_namespace n  ON (n.oid = t.relnamespace) WHERE relkind =
'r'   AND t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname, t.relname, c.conname;*


*-- TABLES and FKEYS*













*SELECT n.nspname as schema,t.relname as table,c.conname as
fk_name  FROM pg_class t  JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE
relkind = 'r'   AND t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE
'sql_%'   ORDER BY n.nspname, t.relname, c.conname;*
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is 

[GENERAL] WAL replication wrong collate

[basti]

Hello,

i try to replicate my database. what i have done?

- create a cluster on slave (UTF8, en_US.utf8 collate/c_type)
- stop cluster and cleanup datadir
- do basebackup from master
- start db-cluster

Master has utf8, en_us.uft8 collate/c_type
Now my db on slave has UTF8, c.utf8 collate/c_type.
Whats wrong here?

master and slave had set the same locales.
I dont unterstand that i can create a database in en_us.utf8 and then 
when i did the basebackup it's change to c locale.

I cant find any option for pg_basebackup to set locale/collate.
I use this howto https://wiki.postgresql.org/wiki/Streaming_Replication 
and had install an other cluster in the past with successful.


How can help please?

Best regards,
basti



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Queries for Diagramming Schema Keys

[stimits]

Hi,
 
I'm trying to write an application (using libpqxx/C++) which creates graphical 
images of large and complex relations between tables (basically an SVG image 
with hot spots for drilling down on details). I need to essentially create 
icons of tables and their columns, and then draw interconnecting lines where 
foreign keys are involved, and to distinctly label primary keys, foreign keys, 
and non-key columns. Eventually this will have knowledge of an XML file loading 
scheme and be able to reverse engineer the required load order (imagine 
approximately 1,000 tables with many foreign keys and file loads which may take 
hours for each failure to load). I need some advice on using 
ANSI/information_schema queries to accomplish this.
 
Thus I have this query to list all tables:
SELECT table_name  FROM information_schema.tablesWHERE table_schema='public'   
AND table_type='BASE TABLE';
 
...this seems to work ok. The next query is to find all foreign keys...this 
seems to sort of work, but may have issues:
SELECTtc.table_name AS local_table,kcu.column_name AS key_column,
ccu.table_name AS fk_table,ccu.column_name AS fk_column FROM 
information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (SELECT table_name  FROM 
information_schema.tablesWHERE table_schema='public'   AND table_type='BASE 
TABLE');
 
This is my query to find all primary keys which are not foreign keys, and this 
definitely is not 100% correct:
SELECT DISTINCTtc.table_name AS local_table,kcu.column_name AS 
key_columnFROM information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'PRIMARY 
KEY' AND tc.table_nameIN (SELECT table_name  FROM information_schema.tables 
WHERE table_schema='public'   AND table_type='BASE TABLE')AND (tc.table_name, 
kcu.column_name)NOT IN (SELECTtc.table_name,kcu.column_nameFROM
information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (SELECT table_name  FROM information_schema.tables 
WHERE table_schema='public'   AND table_type='BASE TABLE'))ORDER BY 
local_table, key_column;
 
I am completely at a loss how I would query for all columns which are neither 
primary nor foreign keys. Would anyone have a suggestion for something like 
this:
SELECT table_name, non_key_column
FROM ...
WHERE ...
ORDER BY table_name, non_key_column
 
Any advice on any of the queries would be appreciated!
 
Thanks!

Re: [GENERAL] Development of an extension for PostgreSQL and PostGIS

[Paul Ramsey]

In order to get an LWGEOM from PostGIS you'll need to convert from the
serialized form (GSERIALIZED) which you can read all about in the
liblwgeom.h header. You'll be adding a hard dependency of course, but
hopefully you're OK with that.

If you're just hoping to build a compound type, as your example shows, you
can do that without a C extension, just read up on CREATE TYPE.

For an alternate example of an extension with a lighter dependency on
PostGIS, check out pgpointcloud, which has it's own structure for spatial
data (a point patch) and exchanges data with PostGIS via well-known-binary.
This removes the liblwgeom dependency, which means it's possible to compile
and use pgpointcloud without PostGIS installed, which is not entirely
uncommon.

P


On Mon, Aug 14, 2017 at 11:18 AM, Fabiana Zioti 
wrote:

> Hello.
>
> I will start developing an extension to PostgreSQL next to PostGIS using
> the C language.
>
> If my new type were:
>
>
> CREATE TYPE mytype (.., .., .., geom geometry);
>
> The creation of the structure in c, would be something like?
>
> #include "liblwgeom.h"
>
> Struct mytype
> {
>Int32 id;
>LWGEOM lwgeom;
>
> };
>
>
> In the extension I will create new data types for PostgreSQL, but I would
> like to use the geometric objects that the PostGIS extension offers, such
> as POINT, LINE, POLYGON, etc. In addition to their input functions (wkt-
> ST_GeomFromText ()), operators, index, etc.
>
> In this case just importing the liblwgeom library would be enough to
> develop an extension to PostgreSQL / PostGIS?
>
> Would you have any examples of such a project?
>
> Thanks in advance!!
>
>

[GENERAL] Development of an extension for PostgreSQL and PostGIS

[Fabiana Zioti]

Hello.

I will start developing an extension to PostgreSQL next to PostGIS using the C 
language.

If my new type were:


CREATE TYPE mytype (.., .., .., geom geometry);

The creation of the structure in c, would be something like?

#include "liblwgeom.h"

Struct mytype
{
   Int32 id;
   LWGEOM lwgeom;

};


In the extension I will create new data types for PostgreSQL, but I would like 
to use the geometric objects that the PostGIS extension offers, such as POINT, 
LINE, POLYGON, etc. In addition to their input functions (wkt- ST_GeomFromText 
()), operators, index, etc.

In this case just importing the liblwgeom library would be enough to develop an 
extension to PostgreSQL / PostGIS?

Would you have any examples of such a project?

Thanks in advance!!

[GENERAL] PostgreSQL used in our network engine (SLikeNet)

[Stefan Hett]

Hi,

I'd just like to quickly reach out to let you know that we released a
new (open source) network engine in which we also utilize PostgreSQL and want 
to thank you for the work you put into the project.

PostgreSQL is utilized for two parts in the network engine:
- as an option for a backend db behind an autopatching system to allow 
synchronizing files between different peers
- as an option for a backend db behind a lobby system to handle/manage things 
like friends and room-management

In order to not have this acknowledgement mail be misinterpreted as an
advertisement mail from our side, I refrained from adding links to the
project pages. If you are interested in the project, feel free to send
me a mail and I'll give you the links to the project pages.

Please note that I'm not subscribed to the mailing list and would appreciate if 
you could CC me directly if you choose to reply to this mail.

-- 
Regards,
Stefan Hett, CEO SLikeSoft

SLikeSoft UG (haftungsbeschränkt), Dresdener Str. 8, 52068 Aachen, Germany
Tel: +49 157 74050303
Geschäftsführer: Stefan Hett, Handelsregister Aachen HRB 20706



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Adding a new Clause in the Source Code

[Peter Eisentraut]

On 8/14/17 04:14, Stefan Wagner wrote:
> I would really appreciate if someone could hack me a quick example of a
> new clause lets call it ADDITIONAL with Keywords HIGH, LOW to order
> columns. I just want the construct. What
> the Keywords accomplish and the Algorithm is something I'm gonna try to
> figure out on my own. I just need to know in which File and which Line I
> need to enter my Code.

For projects like this, figuring out all the places where to change
things is often half the work.

If I were to approach this, I would see how ORDER BY is implemented and
track all those places down and then see whether I need to make
analogous changes there.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Adding a new Clause in the Source Code

[Stefan Wagner]

Recently I started to take a look at Postgresql on the Source Level. I wanted to extend the Parser by a simple Clause which isn't as easy as I expected.
Hence I'm here asking for help. This might seem trivial for a Person which is able to answer this Question but please take a moment of your time and help a
fellow coder out. 

 

My Objective is to add a new Clause after the WHERE Clause in a SELECT Statement which modifys the order of my result. I allready invested some time to get familar
with the Bison part in source\src\backend\parser so adding new Keywords seems possible. The question now is in which Files do I need to add what to make it work.
Yes this question seems a bit broad but Information I found was confusing or outdated.

 

I would really appreciate if someone could hack me a quick example of a new clause lets call it ADDITIONAL with Keywords HIGH, LOW to order columns. I just want the construct. What
the Keywords accomplish and the Algorithm is something I'm gonna try to figure out on my own. I just need to know in which File and which Line I need to enter my Code.

 

I appreciate any help I can get and if needed I'll add some additional Information.

Re: [GENERAL] Where is pg_hba.conf

[Arthur Zakirov]

Hello,

On Sun, Aug 13, 2017 at 10:00:23AM -0400, Igor Korot wrote:
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
> 
> Can someone please help?

You can execute the query:

=# select setting from pg_settings where name = 'hba_file';

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cluster question

[Andreas Kretschmer]

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad  wrote:
>Hi
>
>I have setup a streaming replicating cluster, with a hot standby.
>
>Now I would like to change the RW to hot standby and change the hot
>standby
>to be the RW server.
>
>Is it just a matter of updating recover.conf file ?
>
>Alex

I would suggest you repmgr, with this tool you can do "repmgr standby 
switchover" to perform such tasks.

Regards, Andreas.


-- 
2ndQuadrant - The PostgreSQL Support Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] cluster question

[Alex Samad]

Hi

I have setup a streaming replicating cluster, with a hot standby.

Now I would like to change the RW to hot standby and change the hot standby
to be the RW server.

Is it just a matter of updating recover.conf file ?

Alex