Re: Is there a way to dump schema to files on disk and keep them in sync

[Ron]

On 12/12/20 8:58 PM, Tim Uckun wrote:

I want to dump my postgres schema to disk in neat directories like
pgadmin presents. Then I want to be able to edit the files and sync
changes to the database and ideally if changes were made in the
database to sync them back to the disk.


That could get really time- and disk-consuming if one of those "edits" was 
to convert a column in a large 500M row table from numeric to text (or vice 
versa), or add a column to the "middle" of a table.


Tricky to program, too, given all the foreign keys, partitions, etc, etc 
that can be part of a table.



Is there a tool that does this? Is there a tool that will dump the
schema into separate directories and files like pgadmin does?


--
Angular momentum makes the world go 'round.

Re: Is there a way to dump schema to files on disk and keep them in sync

[Adrian Klaver]

On 12/12/20 6:58 PM, Tim Uckun wrote:

I want to dump my postgres schema to disk in neat directories like
pgadmin presents. Then I want to be able to edit the files and sync
changes to the database and ideally if changes were made in the
database to sync them back to the disk.

Is there a tool that does this? Is there a tool that will dump the
schema into separate directories and files like pgadmin does?


pgAdmin does not create directories, it just organizes the contents of 
the system catalogs into GUI elements.


For schema management I would suggest a tool like the one I use 
Sqitch(https://sqitch.org/). It will organize the process of schema 
creation and management.




Thanks.





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

Re: Is there a way to dump schema to files on disk and keep them in sync

[Tomas Vondra]

On 12/13/20 6:34 PM, Adrian Klaver wrote:
> On 12/12/20 6:58 PM, Tim Uckun wrote:
>> I want to dump my postgres schema to disk in neat directories like
>> pgadmin presents. Then I want to be able to edit the files and sync
>> changes to the database and ideally if changes were made in the
>> database to sync them back to the disk.
>>
>> Is there a tool that does this? Is there a tool that will dump the
>> schema into separate directories and files like pgadmin does?
> 
> pgAdmin does not create directories, it just organizes the contents of
> the system catalogs into GUI elements.
> 
> For schema management I would suggest a tool like the one I use
> Sqitch(https://sqitch.org/). It will organize the process of schema
> creation and management.
> 

Yeah, that was my thought too. Or maybe look at the other schema
versioning tools available - we have a list on the wiki:

https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques

I'm sure it's incomplete, but it's helpful nevertheless.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: pg_restore fails when psql succeeds

[Adrian Klaver]

On 12/12/20 12:39 PM, Cherio wrote:
I install PostgreSQL from "apt.postgresql.org 
" repository:

$ /usr/lib/postgresql/13/bin/postgres --version
postgres (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1)

It runs in a VM, the most recent & updated version of Ubuntu LTS 20.04
$ uname -a
Linux dbwfprod8-20 5.4.0-56-generic #62-Ubuntu SMP Mon Nov 23 19:20:19 
UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

RAM: 48GB
CPU: 8
Storage: 800GB (plenty of free space left)

I attached customizations to postgresql.conf and a few relevant errors 
from the PostgreSQL server log; there were no relevant messages in 
journalctl.





What is the exact command you are using to do the restore?


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

Re: Is there a way to dump schema to files on disk and keep them in sync

[Benedict Holland]

You want Alembic and an afternoon of python writing. You just described an
ORM.

On Sun, Dec 13, 2020, 12:53 PM Tomas Vondra 
wrote:

> On 12/13/20 6:34 PM, Adrian Klaver wrote:
> > On 12/12/20 6:58 PM, Tim Uckun wrote:
> >> I want to dump my postgres schema to disk in neat directories like
> >> pgadmin presents. Then I want to be able to edit the files and sync
> >> changes to the database and ideally if changes were made in the
> >> database to sync them back to the disk.
> >>
> >> Is there a tool that does this? Is there a tool that will dump the
> >> schema into separate directories and files like pgadmin does?
> >
> > pgAdmin does not create directories, it just organizes the contents of
> > the system catalogs into GUI elements.
> >
> > For schema management I would suggest a tool like the one I use
> > Sqitch(https://sqitch.org/). It will organize the process of schema
> > creation and management.
> >
>
> Yeah, that was my thought too. Or maybe look at the other schema
> versioning tools available - we have a list on the wiki:
>
> https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques
>
> I'm sure it's incomplete, but it's helpful nevertheless.
>
>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>

Re: Is there a way to dump schema to files on disk and keep them in sync

[Adrian Klaver]

On 12/13/20 10:03 AM, Benedict Holland wrote:
You want Alembic and an afternoon of python writing. You just described 
an ORM.


In other words out of the frying pan and into the fire.



On Sun, Dec 13, 2020, 12:53 PM Tomas Vondra 
mailto:tomas.von...@enterprisedb.com>> 
wrote:


On 12/13/20 6:34 PM, Adrian Klaver wrote:
 > On 12/12/20 6:58 PM, Tim Uckun wrote:
 >> I want to dump my postgres schema to disk in neat directories like
 >> pgadmin presents. Then I want to be able to edit the files and sync
 >> changes to the database and ideally if changes were made in the
 >> database to sync them back to the disk.
 >>
 >> Is there a tool that does this? Is there a tool that will dump the
 >> schema into separate directories and files like pgadmin does?
 >
 > pgAdmin does not create directories, it just organizes the
contents of
 > the system catalogs into GUI elements.
 >
 > For schema management I would suggest a tool like the one I use
 > Sqitch(https://sqitch.org/ ). It will
organize the process of schema
 > creation and management.
 >

Yeah, that was my thought too. Or maybe look at the other schema
versioning tools available - we have a list on the wiki:

https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques


I'm sure it's incomplete, but it's helpful nevertheless.


regards

-- 
Tomas Vondra

EnterpriseDB: http://www.enterprisedb.com 
The Enterprise PostgreSQL Company





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

Re: pg_restore fails when psql succeeds

[Cherio]

The exact command is nothing fancy really. The complete exact command is
below

export PGPASSWORD=xxx
pg_restore --verbose --no-password --clean --if-exists -h 111.111.111.111
-p 5432 -U user -d blankdbfromstandardtemplate0 /PATH/EXPORTEDDIR 2>&1 |
tee logfile

I was able to import with pg_restore eventually after I disabled huge_pages
on the OS level. Huge pages were initially configured exactly as suggested
here:
https://www.postgresql.org/docs/13/kernel-resources.html#LINUX-HUGE-PAGES.
This is a dedicated postgres DB server machine but I always verify memory
settings to leave a little wiggle room; postgresqltuner.pl report was clear.


On Sun, Dec 13, 2020 at 1:00 PM Adrian Klaver 
wrote:

> On 12/12/20 12:39 PM, Cherio wrote:
> > I install PostgreSQL from "apt.postgresql.org
> > " repository:
> > $ /usr/lib/postgresql/13/bin/postgres --version
> > postgres (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1)
> >
> > It runs in a VM, the most recent & updated version of Ubuntu LTS 20.04
> > $ uname -a
> > Linux dbwfprod8-20 5.4.0-56-generic #62-Ubuntu SMP Mon Nov 23 19:20:19
> > UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
> > RAM: 48GB
> > CPU: 8
> > Storage: 800GB (plenty of free space left)
> >
> > I attached customizations to postgresql.conf and a few relevant errors
> > from the PostgreSQL server log; there were no relevant messages in
> > journalctl.
> >
> >
>
> What is the exact command you are using to do the restore?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Is there a way to dump schema to files on disk and keep them in sync

[Tim Uckun]

My primary use case for this is for development and experimentation, I
have no intent on using it on production servers :)

I normally use migrations for those.

On Sun, Dec 13, 2020 at 9:04 PM Ron  wrote:
>
> On 12/12/20 8:58 PM, Tim Uckun wrote:
> > I want to dump my postgres schema to disk in neat directories like
> > pgadmin presents. Then I want to be able to edit the files and sync
> > changes to the database and ideally if changes were made in the
> > database to sync them back to the disk.
>
> That could get really time- and disk-consuming if one of those "edits" was
> to convert a column in a large 500M row table from numeric to text (or vice
> versa), or add a column to the "middle" of a table.
>
> Tricky to program, too, given all the foreign keys, partitions, etc, etc
> that can be part of a table.
>
> > Is there a tool that does this? Is there a tool that will dump the
> > schema into separate directories and files like pgadmin does?
>
> --
> Angular momentum makes the world go 'round.
>
>

Re: Is there a way to dump schema to files on disk and keep them in sync

[Tim Uckun]

>pgAdmin does not create directories, it just organizes the contents of
the system catalogs into GUI elements.


I realize that :). I meant organized in the same way but on disk.

Re: Is there a way to dump schema to files on disk and keep them in sync

[Adrian Klaver]

On 12/13/20 1:04 PM, Tim Uckun wrote:

pgAdmin does not create directories, it just organizes the contents of

the system catalogs into GUI elements.


I realize that :). I meant organized in the same way but on disk.



Nothing that I know of. You could do a pg_dump -d some_db 
-s/--schema-only and to get a starting point for schema definitions. 
Then create your own directory structure for the schema types(tables, 
functions, types, etc), with sub-directories for the individual items. I 
did something like that years ago and it worked until it fell over. For 
instance should triggers definitions stay with the table or be on their 
own, same for trigger functions. What happens if you rename something, 
do you rename your directory? And so on. That is why I moved to Sqitch, 
someone smarter then I came up with a solution that worked and allows me 
to do what you state is your goal in your previous post. Namely, use for 
development and experimentation. In addition once you have done the 
preceding can then roll out to production.


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

Re: Is there a way to dump schema to files on disk and keep them in sync

[Benedict Holland]

I mean, you want to dump a schema into a directory. Alembic, sqlalchemy and
pathlib will do that in a few minutes. If you want to sync changes then
write alembic change scripts and reflect those changes in the ORM, alembic
does that. Modifying the ORM and reflecting those changes is a terrible
idea and no one does it well. Every application has huge caveats baked into
their application so I recommend not doing that. Buy seriously, this is a
few hours of work or less.

Thanks,
Ben

On Sun, Dec 13, 2020, 4:05 PM Tim Uckun  wrote:

> >pgAdmin does not create directories, it just organizes the contents of
> the system catalogs into GUI elements.
>
>
> I realize that :). I meant organized in the same way but on disk.
>
>
>

Re: Potential BRIN Index Corruption

[Huan Ruan]

Hi Tomas

The records have a timestamp column so we do know the time they were
written. We didn't find any I/O issues that match that time but
unfortunately as it's been a while we are not confident with that finding.

>  Are there any other corrupted indexes on the table?

That was one of my first questions too. I don't see any physical errors in
pg log so not sure if there are other corruptions. One thing we consider
doing is to turn on checksums.

Regards
Huan

Dynamic procedure execution

[Muthukumar.GK]

Hi team,

When I am trying to implement belwo dynamic concept in postgreSql, getting
some error. Kindly find the below attached program and error. Please advise
me what is wrong here..

CREATE OR REPLACE PROCEDURE DynamicProc()

AS $$

DECLARE v_query TEXT;

C1 refcursor := 'result1';

begin



v_query := '';

v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';



EXECUTE (v_query);

END;

$$

Language plpgsql;



 Calling procedure :-



CALL DynamicProc();

FETCH ALL IN "result1";


Error :-

--



ERROR: syntax error at or near "OPEN"

LINE 1: OPEN C1 FOR SELECT * FROM public."Los_BankInfo" ^



QUERY: OPEN C1 FOR SELECT * FROM public."Los_BankInfo"

CONTEXT: PL/pgSQL function dynamicproc() line 9 at EXECUTE SQL state: 42601


Regards

Muthukumar.gk

Re: Dynamic procedure execution

[David G. Johnston]

On Sunday, December 13, 2020, Muthukumar.GK  wrote:

>
> v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';
>
>
>
> EXECUTE (v_query);
>
>
>
You put the pl/pgsql OPEN command into a string and sent it to the SQL
engine via EXECUTE and the SQL engine is complaining that it has no idea
what you want it to do.

David J.

Re: Dynamic procedure execution

[Muthukumar.GK]

Hi David,

As I am not bit Clea, let me know what I have to do. If possible, please
re- write my program.

Regards
Muthu

On Mon, Dec 14, 2020, 11:43 AM David G. Johnston 
wrote:

> On Sunday, December 13, 2020, Muthukumar.GK  wrote:
>
>>
>> v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';
>>
>>
>>
>> EXECUTE (v_query);
>>
>>
>>
> You put the pl/pgsql OPEN command into a string and sent it to the SQL
> engine via EXECUTE and the SQL engine is complaining that it has no idea
> what you want it to do.
>
> David J.
>
>