Re: [GENERAL] Querying dead rows

[Stephen Frost]

* Rakesh Kumar (rakeshkumar...@outlook.com) wrote:
> Is there a way to query dead rows (that is, rows which are dead and still not 
> cleaned up by Vacuum) using SQL.  I am asking this just as an academical 
> question.

CREATE EXTENSION pageinspect;

https://www.postgresql.org/docs/9.6/static/pageinspect.html

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] [OT] Postgresql and PHP

[rob stone]

Hello,
On Fri, 2016-12-23 at 16:44 +0100, Alessandro Baggi wrote:
> Hi list,
> sorry for this OT.
> 
> I have a table on postgresql like this:
> 
> id serial not null,
> srcaddr varchar(16) not null
> 
> I use this table to store ip address. I've used also inet type but 
> changed to see if this solves my problem.
> 
>  From psql, I run:
> 
> select count(*) from bs_ipsource where srcaddr = '192.168.1.1';
> 
> 

I store and retrieve IP Addresses as follows:-

id serial not null,
srcaddr inet not null

select count(*) from bs_ipsource where srcaddr = '192.168.1.1'::inet;

Cast the textual representation to inet.

Never had a problem.

HTH,
Rob


-- 
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] [OT] Postgresql and PHP

[Adrian Klaver]

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
mailto:alessandro.ba...@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG:  execute : SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL:  parameters: $1 = '192.168.1.1'


​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.



Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.

Then I've only this db and only this host.


Another thought, do you have this table in more then one schema and 
maybe you are running into a search_path issue:


https://www.postgresql.org/docs/9.5/static/runtime-config-client.html











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


--
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] [OT] Postgresql and PHP

[Adrian Klaver]

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
mailto:alessandro.ba...@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG:  execute : SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL:  parameters: $1 = '192.168.1.1'


​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.



Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.


Might want to set log_statement to 'all' at least temporarily:

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT



Then I've only this db and only this host.









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


--
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] Er Data Modeller for PostgreSQL

[Edmundo Robles]

Maybe visual paradigm could help you,  you can reverse and generate the
database in postgresql.

On Thu, Dec 22, 2016 at 11:50 PM, Günce Kaya  wrote:

> Hi All,
>
> I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
> Premium for postgresql and the tool has a modeller but I would like to
> display a database modeller that belonging to a tables of an schema under a
> database.
>
> If I use Navicat for modeller, I have to drag and drop whole tables which
> I want to add to data modeller. So It's pretty manual process.
>
> Is there any way to display an er modeller that show only chosen schema
> under the database?
>
> Any advice would be appreciated.
>
> Regards,
>
> --
> Gunce Kaya
>

Re: [GENERAL] Bug? Netmask of CIDR as TEXT has trailing masklen

[Tom Lane]

no...@null.net writes:
> Ah, I just noticed that the return value of the netmask() function is
> of type 'inet' and that is (in my mind) where the actual issue is. A
> netmask may have the same underlying form and space requirements as an
> internet address or subnet, but it isn't really the same thing.

Maybe, but we're not going to invent a whole new datatype just to
represent that.  Perhaps you would be happier using the masklen()
function.

regards, tom lane


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

[GENERAL] explain analyze showed improved results without changes, why?

[Chris Richards]

Howdy. I was noticing a significant problem with a query on one of my
tables. I tried recreating the problem and wasn't able to do so on a
different install, and so a few days later I ran the same query on the
problem table. Lo' and behold, there wasn't a problem anymore. I'm at a
loss to why. The indices and two explains follow below. Thanks in advance
for the help.

Cheers,
Chris

Indexes:
"blocks_pkey" PRIMARY KEY, btree (cloudidx, blkid)
"blocks_blkid_idx" btree (blkid)
"blocks_cloudidx_idx" btree (cloudidx)
"blocks_off_sz_idx" btree (off, sz)

mdb=> explain analyze SELECT * FROM blocks
 WHERE cloudidx=98038 AND off+sz >= 0 AND state='seeded'
 ORDER BY off LIMIT 1 FOR UPDATE;
  QUERY
PLAN
---
 Limit  (cost=0.43..587.83 rows=1 width=100) (actual
time=4814.579..4814.579 rows=1 loops=1)
   ->  LockRows  (cost=0.43..1358633.99 rows=2313 width=100) (actual
time=4814.577..4814.577 rows=1 loops=1)
 ->  Index Scan using blocks_off_sz_idx on blocks
 (cost=0.43..1358610.86 rows=2313 width=100) (actual
time=4813.498..4814.384 rows=2 loops=1)
   Filter: ((cloudidx = 98038) AND (state =
'seeded'::block_state) AND ((off + sz) >= 0))
   Rows Removed by Filter: 6935023
 Total runtime: 4814.619 ms
(6 rows)

mdb=> select count(*) from blocks;
  count
--
 11052135
(1 row)

mdb=> select count (distinct (cloudidx)) from blocks;
 count
---
  4549
(1 row)

And here's the second. Notice that even though there are more rows, it was
much faster and the "rows removed by filter" were significantly reduced by
several orders of magnitude.

mdb=> explain analyze SELECT * FROM blocks
 WHERE cloudidx=98038 AND off+sz >= 0 AND state='seeded'
 ORDER BY off LIMIT 1 FOR UPDATE;

QUERY 
PLAN-
 Limit  (cost=0.43..584.57 rows=1 width=100) (actual time=0.071..0.071
rows=1 loops=1)
   ->  LockRows  (cost=0.43..1390825.21 rows=2381 width=100) (actual
time=0.070..0.070 rows=1 loops=1)
 ->  Index Scan using blocks_off_sz_idx on blocks
(cost=0.43..1390801.40 rows=2381 width=100) (actual time=0.055..0.055
rows=1 loops=1)
   Filter: ((cloudidx = 98038) AND (state =
'seeded'::block_state) AND ((off + sz) >= 0))
   Rows Removed by Filter: 26
 Total runtime: 0.114 ms(6 rows)

mdb=> select count(*) from blocks;
  count--
 11328801(1 row)

mdb=> select count (distinct (cloudidx)) from blocks;
 count---
  4613(1 row)

Re: [GENERAL] Bug? Netmask of CIDR as TEXT has trailing masklen

[nomad]

On Fri Dec 23, 2016 at 10:15:21AM -0500, Tom Lane wrote:
>
> Yes, it should be: that is the same as "text(netmask('1.1.1.0/24'))",
> and the table of network functions specifically describes text(inet)
> as "extract IP address and netmask length as text".  If you only want
> the IP address, use host(), or possibly abbrev() which I think follows the
> output function's rule of suppressing the netmask when it is full-width.

Ah, I just noticed that the return value of the netmask() function is
of type 'inet' and that is (in my mind) where the actual issue is. A
netmask may have the same underlying form and space requirements as an
internet address or subnet, but it isn't really the same thing.

> From a system-wide consistency standpoint, it's rather unfortunate that
> inet's default conversion to text type does not behave the same as the
> inet output function.  But it's been like that for umpteen years and
> the costs of breaking backwards compatibility would outweigh any benefit
> of changing it.

I can see the consistency issue, but I actually don't have much of an
problem with the fact that 'inet' converts to text as 'addr/mask' by
default.  That at least is still an accurate presentation form. But I
have never been presented with a the concept of a network mask having
its own network mask. That just feels wrong.

Do the same backwards compatibility requirements apply to the result
type of the netmask() function? Perhaps a new 'netmask' type with a
better text conversion is possible?

Mark.
-- 
Mark Lawrence


-- 
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] [OT] Postgresql and PHP

[Alessandro Baggi]

Hi Adrian,



And what is the count?



Reported is 1



How do you know it fails?



Really I don't know if it fails but

$query = pg_query_params();
if(!$query)
{
echo pg_last_error($dbcon);
echo "ERROR";
} else {
$row = pg_fetch_assoc($query);
if(!$row)
{
echo "some error";
}

}

does not help to check if there are errors?

With this code I block in the first statement with "ERROR" string 
printed and not real error. I've also tried to see if pg_last_error() 
works with query keywords errors  (Select * from nonexisttable) and works.






--
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] [OT] Postgresql and PHP

[Alessandro Baggi]

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
mailto:alessandro.ba...@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG:  execute : SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL:  parameters: $1 = '192.168.1.1'


​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.



Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other 
tables and works without problem.


Then I've only this db and only this host.






--
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] [OT] Postgresql and PHP

[Raymond O'Donnell]

On 23/12/16 15:44, Alessandro Baggi wrote:

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.

From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.

From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}


Are you sure it's failing? Try this:

if ($query === false) {
  
}

PHP has funny ideas about what's true and false.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


--
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] [OT] Postgresql and PHP

[David G. Johnston]

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi <
alessandro.ba...@gmail.com> wrote:

> $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
> srcaddr = $1", array($ipsrc));
> if(!$query) { print error...}
>
> I don't understand why this query fails without error. I have searched
> error in httpd logs and postgresql log without success.
>
> from postgresql logs i get:
>
> LOG:  execute : SELECT count(*) from bs_ipsource where srcaddr =
> $1
> DETAIL:  parameters: $1 = '192.168.1.1'
>

​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which has
the relevant data, is not the same database that PHP is talking to, and
which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.

Re: [GENERAL] [OT] Postgresql and PHP

[Adrian Klaver]

On 12/23/2016 07:44 AM, Alessandro Baggi wrote:

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.

From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.


And what is the count?



From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have searched


How do you know it fails?


error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG:  execute : SELECT count(*) from bs_ipsource where srcaddr
= $1
DETAIL:  parameters: $1 = '192.168.1.1'


I've tried also to use pg_last_error and errors are not printed.

Can someone put me on the right way?

Thanks in advance.





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


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

[GENERAL] [OT] Postgresql and PHP

[Alessandro Baggi]

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but 
changed to see if this solves my problem.


From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.

From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where 
srcaddr = $1", array($ipsrc));

if(!$query) { print error...}

I don't understand why this query fails without error. I have searched 
error in httpd logs and postgresql log without success.


from postgresql logs i get:

LOG:  execute : SELECT count(*) from bs_ipsource where srcaddr = $1
DETAIL:  parameters: $1 = '192.168.1.1'


I've tried also to use pg_last_error and errors are not printed.

Can someone put me on the right way?

Thanks in advance.


--
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] Bug? Netmask of CIDR as TEXT has trailing masklen

[Tom Lane]

no...@null.net writes:
> The following displays as I would expect:
> mark=# select netmask('1.1.1.0/24');
> netmask
> ---
>  255.255.255.0
> (1 row)

> However the following does not look right:

> mark=# select netmask('1.1.1.0/24')::text;
>  netmask  
> --
>  255.255.255.0/32
> (1 row)

> The trailing "/32" should not be there.

Yes, it should be: that is the same as "text(netmask('1.1.1.0/24'))",
and the table of network functions specifically describes text(inet)
as "extract IP address and netmask length as text".  If you only want
the IP address, use host(), or possibly abbrev() which I think follows the
output function's rule of suppressing the netmask when it is full-width.

>From a system-wide consistency standpoint, it's rather unfortunate that
inet's default conversion to text type does not behave the same as the
inet output function.  But it's been like that for umpteen years and
the costs of breaking backwards compatibility would outweigh any benefit
of changing it.

regards, tom lane


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

[GENERAL] Querying dead rows

[Rakesh Kumar]

Is there a way to query dead rows (that is, rows which are dead and still not 
cleaned up by Vacuum) using SQL.  I am asking this just as an academical 
question.


-- 
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] Is there a reason the "-v/verbose" switch is not printed with pg_dumpall --help

[Stephen Frost]

Daniel,

* Daniel Westermann (daniel.westerm...@dbi-services.com) wrote:
> postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall -V 
> pg_dumpall (PostgreSQL) 9.6.1 
> postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall --help 
> pg_dumpall extracts a PostgreSQL database cluster into an SQL script file. 

Hmm.  Looks like an oversight, will see about fixing it.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Günce Kaya]

Hi,

Thank you for your response.

This document is pretty good to use SchemasPy and thank you for your
sharing but I could not integrate with our postgres yet. I'm looking for a
database tool like Toad, DBVisualizer so on. It'll be more useful to me
now.

Regards,

Gunce Kaya

2016-12-23 12:44 GMT+03:00 Thomas Kellerer :

> Stephen Davies schrieb am 23.12.2016 um 10:08:
>
>> I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
>>> Premium
>>> for postgresql and the tool has a modeller but I would like to display a
>>> database modeller that belonging to a tables of an schema under a
>>> database.
>>>
>>> If I use Navicat for modeller, I have to drag and drop whole tables
>>> which I
>>> want to add to data modeller. So It's pretty manual process.
>>>
>>> Is there any way to display an er modeller that show only chosen schema
>>> under
>>> the database?
>>>
>>> Any advice would be appreciated.
>>>
>>
>> Try Schemaspy
>
> Scott Mead just blogged about using SchemaSpy with Postgres
>
> http://www.openscg.com/2016/12/postgresql-schema-visualization/
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Gunce Kaya

Re: [GENERAL] Is is safe to use SPI in multiple threads?

[Peter J. Holzer]

On 2016-12-09 16:52:05 +0800, Qiu Xiafei wrote:
> I'm new to PG and want to implement my domain-specific system based on PG. I
> wish to arrange my data as several tables in database and translate my DSL 
> into
> SQL statements for query. Since one DSL statement may be mapped to several SQL
> statements, it's better to push the DSL server as close to the PG server as
> possible. I found PG's backgroud worker meet my needs. I can setup a 
> background
> worker bounded to PG server and listen to a port for network requests. 
> 
> But I encounter a problem that the Server Programing Interfaces are not THREAD
> SAFE. There are some global variables defined like: SPI_processed,
> SPI_tuptable, etc. This limit to my DSL server to work in single thread mode
> which is quite inefficient.

I had a similar requirement. I solved it by moving the application logic
out of the stored procedures. All the stored procedure does is an RPC
call (I use ØMQ for that) to a server process and send the result back
to the client. The server process converts the request into multiple SQL
queries which can be processed in parallel.

The downside is of course that the communication overhead is much
higher (A minimum of 4 network messages per request). That's not a
problem in my case, but you mileage may vary.

The advantages in my opinion are:

* A standalone server process is easier to test and debug than a bunch
  of stored procedures.
* I can easily scale out if necessary: Currently my database and server
  process run on the same machine, but I could distribute them over
  several machines with (almost) no change in logic.

hp

-- 
   _  | Peter J. Holzer| A coding theorist is someone who doesn't
|_|_) || think Alice is crazy.
| |   | h...@hjp.at | -- John Gordon
__/   | http://www.hjp.at/ |http://downlode.org/Etext/alicebob.html


signature.asc
Description: Digital signature

[GENERAL] Bug? Netmask of CIDR as TEXT has trailing masklen

[nomad]

The following displays as I would expect:

mark=# select netmask('1.1.1.0/24');
netmask
---
 255.255.255.0
(1 row)

However the following does not look right:

mark=# select netmask('1.1.1.0/24')::text;
 netmask  
--
 255.255.255.0/32
(1 row)

The trailing "/32" should not be there. I have been trying to compare
TEXT strings like '255.255.255.0' with the netmask of a CIDR column and
the above is making it difficult.

Regards,
Mark
-- 
Mark Lawrence


-- 
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] Er Data Modeller for PostgreSQL

[Martijn Tonies (Upscene Productions)]

Hello Gunce,

Do you mean some sort of reverse engineering? Database Workbench supports that.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

From: Andreas Joseph Krogh 
Sent: Friday, December 23, 2016 11:57 AM
To: pgsql-general@postgresql.org 
Subject: Re: [GENERAL] Er Data Modeller for PostgreSQL

På fredag 23. desember 2016 kl. 06:50:54, skrev Günce Kaya 
:
  Hi All, 

  I'm looking for an ER Data Modeller tool for postgresql. I use Navicat 
Premium for postgresql and the tool has a modeller but I would like to display 
a database modeller that belonging to a tables of an schema under a database.

  If I use Navicat for modeller, I have to drag and drop whole tables which I 
want to add to data modeller. So It's pretty manual process. 

  Is there any way to display an er modeller that show only chosen schema under 
the database? 

  Any advice would be appreciated. 

  Regards,

  -- 
  Gunce Kaya

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Andreas Joseph Krogh]

På fredag 23. desember 2016 kl. 06:50:54, skrev Günce Kaya <
guncekay...@gmail.com >:
Hi All,  
I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium 
for postgresql and the tool has a modeller but I would like to display a 
database modeller that belonging to a tables of an schema under a database.
 
If I use Navicat for modeller, I have to drag and drop whole tables which I 
want to add to data modeller. So It's pretty manual process. 
 
Is there any way to display an er modeller that show only chosen schema under 
the database? 
 
Any advice would be appreciated. 
 
Regards,
 
-- 
Gunce Kaya



 
We use IDEA from IntelliJ: https://www.jetbrains.com/idea/
It features a nice DB-tool integrated (the commercial version only I think)
 

Database tools

Take advantage of intelligent coding assistance when editing SQL; connect to 
live databases; run queries; browse and expert data; and even manage your 
schemes in a visual interface–right from the IDE.

learn more 
 
 
...which I think is an embeded version of their DataGrip 
application: https://www.jetbrains.com/datagrip/
 

-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Thomas Kellerer]

Stephen Davies schrieb am 23.12.2016 um 10:08:

I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium
for postgresql and the tool has a modeller but I would like to display a
database modeller that belonging to a tables of an schema under a database.

If I use Navicat for modeller, I have to drag and drop whole tables which I
want to add to data modeller. So It's pretty manual process.

Is there any way to display an er modeller that show only chosen schema under
the database?

Any advice would be appreciated.


Try Schemaspy


Scott Mead just blogged about using SchemaSpy with Postgres

http://www.openscg.com/2016/12/postgresql-schema-visualization/



--
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] Er Data Modeller for PostgreSQL

[Stephen Davies]

On 23/12/16 16:20, Günce Kaya wrote:

Hi All,

I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium
for postgresql and the tool has a modeller but I would like to display a
database modeller that belonging to a tables of an schema under a database.

If I use Navicat for modeller, I have to drag and drop whole tables which I
want to add to data modeller. So It's pretty manual process.

Is there any way to display an er modeller that show only chosen schema under
the database?

Any advice would be appreciated.

Regards,

--
Gunce Kaya


Try Schemaspy




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

[GENERAL] Is there a reason the "-v/verbose" switch is not printed with pg_dumpall --help

[Daniel Westermann]

postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall -V 
pg_dumpall (PostgreSQL) 9.6.1 
postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall --help 
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file. 

Usage: 
pg_dumpall [OPTION]... 

General options: 
-f, --file=FILENAME output file name 
-V, --version output version information, then exit 
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock 
-?, --help show this help, then exit 

Options controlling the output content: 
-a, --data-only dump only the data, not the schema 
-c, --clean clean (drop) databases before recreating 
-g, --globals-only dump only global objects, no databases 
-o, --oids include OIDs in dump 
-O, --no-owner skip restoration of object ownership 
-r, --roles-only dump only roles, no databases or tablespaces 
-s, --schema-only dump only the schema, no data 
-S, --superuser=NAME superuser user name to use in the dump 
-t, --tablespaces-only dump only tablespaces, no databases or roles 
-x, --no-privileges do not dump privileges (grant/revoke) 
--binary-upgrade for use by upgrade utilities only 
--column-inserts dump data as INSERT commands with column names 
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting 
--disable-triggers disable triggers during data-only restore 
--if-exists use IF EXISTS when dropping objects 
--inserts dump data as INSERT commands, rather than COPY 
--no-security-labels do not dump security label assignments 
--no-tablespaces do not dump tablespace assignments 
--no-unlogged-table-data do not dump unlogged table data 
--quote-all-identifiers quote all identifiers, even if not key words 
--use-set-session-authorization 
use SET SESSION AUTHORIZATION commands instead of 
ALTER OWNER commands to set ownership 

Connection options: 
-d, --dbname=CONNSTR connect using connection string 
-h, --host=HOSTNAME database server host or socket directory 
-l, --database=DBNAME alternative default database 
-p, --port=PORT database server port number 
-U, --username=NAME connect as specified database user 
-w, --no-password never prompt for password 
-W, --password force password prompt (should happen automatically) 
--role=ROLENAME do SET ROLE before dump 

If -f/--file is not used, then the SQL script will be written to the standard 
output. 

Report bugs to . 

It is mentioned in the documentation but not in the help output 

Regards 
Daniel