Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 10:29, Andreas Kretschmer 
wrote:

> Killian Driscoll  wrote:
>
> > I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and
> postgreSQL
> > 9.4 on port 5532 with the latter set up to use with Bitnami stack to
> test php
> > files I am generating from my db.
> >
> > I want to transfer my db with three schemas from port 5432 to port 5532
> to use
> > within the bitnami stack. I have used pgAdmin to create a backup.sql and
> when
> > using pgAdmin to restore the .sql to port 5532 I get the following error
>
> Try it with plain pg_dump.
>
> pg_dump -h localhost -p 5432 -Fc  > dump.sql
>
> pg_restore -h localhost -p 5532 dump.sql
>

I tried this, but nothing appears to happen when entering the commands.
Attached is a screenshot of the shell window - what am I doing wrong?

>
> (untestet, please read *before* the documentation)
>
>
> I think, this should work. No idea what's wrong with pgAdmin, not using
> that.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Transfer db from one port to another

[Charles Clavadetscher]

Hello Killian

>> I want to transfer my db with three schemas from port 5432 to port 5532 to 
>> use
>> within the bitnami stack. I have used pgAdmin to create a backup.sql and when
>> using pgAdmin to restore the .sql to port 5532 I get the following error
>
>Try it with plain pg_dump.
>
>pg_dump -h localhost -p 5432 -Fc  > dump.sql
>
>pg_restore -h localhost -p 5532 dump.sql
>
>I tried this, but nothing appears to happen when entering the commands. 
>Attached is a screenshot of the shell window - what am I doing wrong? 

This should be done from an OS shell, not from psql.

Bye
Charles




(untestet, please read *before* the documentation)


I think, this should work. No idea what's wrong with pgAdmin, not using
that.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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




-- 
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 10:58, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

> Hello Killian
>
> >> I want to transfer my db with three schemas from port 5432 to port 5532
> to use
> >> within the bitnami stack. I have used pgAdmin to create a backup.sql
> and when
> >> using pgAdmin to restore the .sql to port 5532 I get the following error
> >
> >Try it with plain pg_dump.
> >
> >pg_dump -h localhost -p 5432 -Fc  > dump.sql
> >
> >pg_restore -h localhost -p 5532 dump.sql
> >
> >I tried this, but nothing appears to happen when entering the commands.
> Attached is a screenshot of the shell window - what am I doing wrong?
>
> This should be done from an OS shell, not from psql.
>
Do you mean Windows command prompt?

>
> Bye
> Charles
>
>
>
>
> (untestet, please read *before* the documentation)
>
>
> I think, this should work. No idea what's wrong with pgAdmin, not using
> that.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
>
>
> --
> 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] Transfer db from one port to another

[Charles Clavadetscher]

From: Killian Driscoll [mailto:killiandrisc...@gmail.com] 
Sent: Mittwoch, 23. Dezember 2015 11:02
To: Charles Clavadetscher 
Cc: Andreas Kretschmer ; pgsql-general 

Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 10:58, Charles Clavadetscher  > wrote:

Hello Killian

>> I want to transfer my db with three schemas from port 5432 to port 5532 to 
>> use
>> within the bitnami stack. I have used pgAdmin to create a backup.sql and when
>> using pgAdmin to restore the .sql to port 5532 I get the following error
>
>Try it with plain pg_dump.
>
>pg_dump -h localhost -p 5432 -Fc  > dump.sql
>
>pg_restore -h localhost -p 5532 dump.sql
>
>I tried this, but nothing appears to happen when entering the commands. 
>Attached is a screenshot of the shell window - what am I doing wrong?

This should be done from an OS shell, not from psql.

Do you mean Windows command prompt? 

 

  Yes

 


Bye
Charles





(untestet, please read *before* the documentation)


I think, this should work. No idea what's wrong with pgAdmin, not using
that.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


--
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] Transfer db from one port to another

[John R Pierce]

On 12/23/2015 1:40 AM, Killian Driscoll wrote:


Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc  > dump.sql

pg_restore -h localhost -p 5532 dump.sql


I tried this, but nothing appears to happen when entering the 
commands. Attached is a screenshot of the shell window - what am I 
doing wrong?



those are system shell commands, not psql sql commands. catch-22, in the 
windows environment, postgresql's command tools probably aren't in the 
path, so to execute the above commands try this...


start -> run -> *CMD* 

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName>***path "c:\Program Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>*pg_dump -Fc -p 5432 *//*| pg_restore -p 
5532*


if your postgres is installed somewhere else, replace "c:\Program 
Files\PostgreSQL\9.4\bin" in the PATH command with its actual location 
\bin  






--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 11:07, John R Pierce  wrote:

> On 12/23/2015 1:40 AM, Killian Driscoll wrote:
>
> Try it with plain pg_dump.
>>
>> pg_dump -h localhost -p 5432 -Fc  > dump.sql
>>
>> pg_restore -h localhost -p 5532 dump.sql
>>
>
> I tried this, but nothing appears to happen when entering the commands.
> Attached is a screenshot of the shell window - what am I doing wrong?
>
>
>
> those are system shell commands, not psql sql commands.  catch-22, in
> the windows environment, postgresql's command tools probably aren't in the
> path, so to execute the above commands try this...
>
> start -> run ->  *CMD* 
>
> (or, click on an 'Command Prompt' shortcut).
>
> C:\Users\YourName> *path "c:\Program Files\PostgreSQL\9.4\bin";%path%*
> C:\Users\YourName>* pg_dump -Fc -p 5432  | pg_restore -p
> 5532*
>
Thanks. When I do this I get an error: could not find a "pg_dump" to
execute - I've used the path *"C:\Program
Files\PostgreSQL\9.3\bin";%path% *which
appears to be correct

>
>
> if your postgres is installed somewhere else, replace "c:\Program
> Files\PostgreSQL\9.4\bin" in the PATH command with its actual location
> \bin  
>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:19
To: John R Pierce 
Cc: pgsql-general 
Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 11:07, John R Pierce  > wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc  > dump.sql

pg_restore -h localhost -p 5532 dump.sql

 

I tried this, but nothing appears to happen when entering the commands. 
Attached is a screenshot of the shell window - what am I doing wrong? 



those are system shell commands, not psql sql commands.  catch-22, in the 
windows environment, postgresql's command tools probably aren't in the path, so 
to execute the above commands try this...

start -> run ->  CMD 

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> path "c:\Program Files\PostgreSQL\9.4\bin";%path%
C:\Users\YourName> pg_dump -Fc -p 5432  | pg_restore -p 5532

Thanks. When I do this I get an error: could not find a "pg_dump" to execute - 
I've used the path "C:\Program Files\PostgreSQL\9.3\bin";%path% which appears 
to be correct 

 

  You may try calling the app without setting the path first or check the 
location browsing the file system:

  C:\Program Files\PostgreSQL\9.3\bin\ pg_dump -Fc -p 5432 

 

  Don’t forget to replace  with the database that you want to dump.



if your postgres is installed somewhere else, replace "c:\Program 
Files\PostgreSQL\9.4\bin" in the PATH command with its actual location \bin  









-- 
john r pierce, recycling bits in santa cruz

 

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 11:19, Killian Driscoll 
wrote:

> On 23 December 2015 at 11:07, John R Pierce  wrote:
>
>> On 12/23/2015 1:40 AM, Killian Driscoll wrote:
>>
>> Try it with plain pg_dump.
>>>
>>> pg_dump -h localhost -p 5432 -Fc  > dump.sql
>>>
>>> pg_restore -h localhost -p 5532 dump.sql
>>>
>>
>> I tried this, but nothing appears to happen when entering the commands.
>> Attached is a screenshot of the shell window - what am I doing wrong?
>>
>>
>>
>> those are system shell commands, not psql sql commands.  catch-22, in
>> the windows environment, postgresql's command tools probably aren't in the
>> path, so to execute the above commands try this...
>>
>> start -> run ->  *CMD* 
>>
>> (or, click on an 'Command Prompt' shortcut).
>>
>> C:\Users\YourName> *path "c:\Program
>> Files\PostgreSQL\9.4\bin";%path%*
>> C:\Users\YourName>* pg_dump -Fc -p 5432  | pg_restore -p
>> 5532*
>>
> Thanks. When I do this I get an error: could not find a "pg_dump" to
> execute - I've used the path *"C:\Program
> Files\PostgreSQL\9.3\bin";%path% *which appears to be correct
>
Sorry, forgot to add: once I get the warning that the Pg_dump can't be
found there is then a password prompt; I tried the db password and the pc
password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed:
FATAL:  p
assword authentication failed for user "killian"
pg_restore: [archiver] input file is too short (read 0, expected 5)


>
>>
>> if your postgres is installed somewhere else, replace "c:\Program
>> Files\PostgreSQL\9.4\bin" in the PATH command with its actual location
>> \bin  
>>
>>
>>
>>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>>
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 11:36, John R Pierce  wrote:

> On 12/23/2015 2:25 AM, Killian Driscoll wrote:
>
> Sorry, forgot to add: once I get the warning that the Pg_dump can't be
> found there is then a password prompt; I tried the db password and the pc
> password but both fail:
>
> Password:
> pg_dump: [archiver (db)] connection to database "irll_project" failed:
> FATAL:  p
> assword authentication failed for user "killian"
>
>
> note that databases don't have passwords, database USERS have passwords.
> 'killian' probably doesn't have a database user, and since you didn't
> specify a user, it defaulted to your system username (expecting that user
> to have been created in postgres, and wanting that probably non-existant
> postgres users passsword)
>
> so, ok, try the command with -U postgres, as
>
> *pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore -U
> postgres -p 5532*
>
> OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014  08:35   381,952 pg_dump.exe

09/06/2014  08:35   180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project | pg_restore
-U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument


>
> if/when it prompts for a password, thats the password of the 'postgres'
> database user, as configured in the postgres servers.
>
> note it will prompt for the password a couple times,  once for postgres on
> port 5432, and again for postgres on port 5532, at least if both database
> services are configured to require passwords for local connections.
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Re: [GENERAL] Secret Santa List

[Thomas Kellerer]

Lou Duchez schrieb am 23.12.2015 um 04:49:

I have a company with four employees who participate in a Secret
Santa program, where each buys a gift for an employee chosen at
random.  (For now, I do not mind if an employee ends up buying a gift
for himself.) How can I make this work with an SQL statement?

Here is my Secret Santa table:

-- create table secretsanta (giver text, recipient text, primary key
(giver));

insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl'); --

Here is the SQL statement I am using to populate the "recipient"
column:

-- update secretsanta set recipient = ( select giver from secretsanta
s2 where not exists (select * from secretsanta s3 where s3.recipient
= s2.giver) order by random() limit 1 ); --

The problem: every time I run this, a single name is chosen at random
and used to populate all the rows.  So all four rows will get a
recipient of "Steve" or "Earl" or whatever single name is chosen at
random.

I suppose the problem is that the "exists" subquery does not
re-evaluate for each record.  How do I prevent this from happening?
Can I use a "lateral" join of some kind, or somehow tell PostgreSQL
to not be so optimized?



You can populate the table with a single statement:

with people (name) as (
  values ('Frank'), ('Joe'), ('Steve'), ('Earl')
)
insert into secretsanta (giver, recipient)
select distinct on (n1.name) n1.name, n2.name
from people n1
  join people n2 on n1.name <> n2.name
order by n1.name;





--
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] Secret Santa List

[Kevin Grittner]

On Tue, Dec 22, 2015 at 9:49 PM, Lou Duchez  wrote:
> I have a company with four employees who participate in a Secret Santa
> program, where each buys a gift for an employee chosen at random.  (For now,
> I do not mind if an employee ends up buying a gift for himself.)  How can I
> make this work with an SQL statement?
>
> Here is my Secret Santa table:
>
> --
> create table secretsanta
> (giver text,
> recipient text,
> primary key (giver));
>
> insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
> ('Earl');
> --

with
  g as (select giver, row_number() over () as rownum from secretsanta),
  r as (select giver, row_number() over () as rownum from (select
giver from secretsanta order by random()) as x)
update secretsanta
  set recipient = r.giver
  from g join r on g.rownum = r.rownum
  where secretsanta.giver = g.giver;

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Huge delay to finish even having all the records inserted

[Adrian Klaver]

On 12/23/2015 06:49 AM, Alexander Franca Fernandes wrote:

Hi,

I'm inserting 14 millions records from a text file using the Postgre
COPY command.


So how did you execute this?

In other words what is the script you used?



After waiting five days I've realized that all the records seems to be
already inserted,

but the process is still running!!


From what I see it is not running:

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

idle: The backend is waiting for a new client command.


Looks to me whatever you used to run the COPY connected and then never 
disconnected and Postgres is waiting for more instructions on that 
connection.




I don't know if there's any internal postgresql process that impose that
I have to wait even having the records shown in a SELECT query...

I don't know if it's safe to cancel the process right now.

Here's the activity query result (I've cut the columns into lines):

postgres=# SELECT * from pg_stat_activity ;


  datid  |
+
  136776 |
-
  datname  |
--+
  xyzdb|

-
  procpid |
-+-
   303311 |

-
usesysid |
-+-
16387 |

-
usename  |
-+
xyz  |

-

   current_query   |
--+-
 |

--

waiting |
+
f   |

-
   xact_start   |
---+
|

---
   query_start  |
---+
  2015-12-18 12:01:05.553534-06 |

---
  backend_start | client_addr | client_port
---+-+-
  2015-12-18 12:01:05.547759-06 | |  -1


[]s
Alex




--
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 15:30, Adrian Klaver 
wrote:

> On 12/23/2015 06:13 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 14:56, Adrian Klaver > > wrote:
>>
>> On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 11:36, John R Pierce > 
>> >> wrote:
>>
>>  On 12/23/2015 2:25 AM, Killian Driscoll wrote:
>>
>>  Sorry, forgot to add: once I get the warning that the
>> Pg_dump
>>  can't be found there is then a password prompt; I tried
>> the db
>>  password and the pc password but both fail:
>>
>>  Password:
>>  pg_dump: [archiver (db)] connection to database
>> "irll_project"
>>  failed: FATAL:  p
>>  assword authentication failed for user "killian"
>>
>>
>>  note that databases don't have passwords, database USERS have
>>  passwords.  'killian' probably doesn't have a database
>> user, and
>>  since you didn't specify a user, it defaulted to your system
>>  username (expecting that user to have been created in
>> postgres, and
>>  wanting that probably non-existant postgres users passsword)
>>
>>  so, ok, try the command with -U postgres, as *
>>  *
>>
>>  *pg_dump -Fc -p 5432 **-U postgres **irll_project |
>> pg_restore
>>  -U postgres -p 5532*
>>
>> OK - I did the dir and it shows that the dump and restore.exe
>> are there,
>> but running the above gives the below errors
>>
>> 09/06/2014  08:35   381,952 pg_dump.exe
>>
>> 09/06/2014  08:35   180,224 pg_restore.exe
>>
>> C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%
>>
>> C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
>> pg_restore -U po
>> stgres -p 5532
>> ccoulould not findd a n "pg_restore" to executeot find a
>> "pg_dump" to
>> execute
>>
>> pg_restore: [archiver] did not find magic string in file header
>> pg_dump: [custom archiver] could not write to output file:
>> Invalid argument
>>
>>
>>
>> Try breaking the above down into two steps:
>>
>> pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project
>>
>>
>> Doing this step I get response
>> could not find a "pg_dump" to execute
>>
>
> So cd into:
>
> C:\Program Files\PostgreSQL\9.3\bin
>
> and try:
>
> pg_dump --help
>
> that will at least establish that the command is being found.
>

OK - --help on the 9.3 lists help options

>
>
>>
>> pg_restore -U postgres -p 5532 irll_project.out
>>
>>
>>
>>
>>  if/when it prompts for a password, thats the password of the
>>  'postgres' database user, as configured in the postgres
>> servers.
>>
>>  note it will prompt for the password a couple times,  once
>> for
>>  postgres on port 5432, and again for postgres on port 5532,
>> at least
>>  if both database services are configured to require
>> passwords for
>>  local connections.
>>
>>
>>  --
>>  john r pierce, recycling bits in santa cruz
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Shared system resources

[Jim Nasby]

On 12/23/15 7:55 AM, oleg yusim wrote:

Sure David. For simplicity of modeling here, let's assume raw database
data was encrypted and the only possibility for attacker to get
something from raw data is to go and dig into sessions leftovers. Now,
with that has been said, do you happen to know what information actually
gets stored during the session into memory, reserved by session process?
I'm trying to determine, basically, does it even worth a talk - maybe
there is nothing at all valuable.


There's tons of raw data stored in the shared memory segment, and some 
of that can be copied to process local memory at any time. If they OS 
doesn't secure that adequately there's certainly nothing that Postgres 
or any other database can do about it.


As David said, by the time you're concerned about someone getting access 
to raw memory it's already way too late.


As for memory pages being zero'd after they are returned to the OS, 
that's entirely up to the OS. The only thing you could do on the 
Postgres side is to compile with memory debugging enabled, which will 
over-write any memory that's freed with a magic value. That's done to 
help hunt down memory access bugs, but would have the obvious side 
effect of obliterating any data that was in the page.


Uh, only thing is, I don't know if this is done if we're going to be 
returning the memory to the OS.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Shared system resources

[David Wilson]

On Wed, Dec 23, 2015 at 07:07:31AM -0600, oleg yusim wrote:

> May we run into situation, when attacker dumps memory and analyses it
> for valuable content, instead of reserving it for own process, where
> it would be zeroed? My understanding, it is a possibility. Does kernel
> have any safeguard against it?

Sure it might be possible, but they would not have much useful
information about which old processes the pages belonged to, and
besides, they could most likely simply dump memory of a connected client
in this case, or indeed just examine the filesystem or cache to get at
the raw PG database files.

Once someone has this level of access to the system it's not really
useful to model threats much further.

One minor correction from my first mail: MAP_UNINITIALIZED is indeed
accessible to non-root, but as George mentions only when a non-default
kernel parameter has been enabled.


David


-- 
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] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 06:50 AM, Killian Driscoll wrote:

On 23 December 2015 at 15:47, Adrian Klaver From your post I would say the 9.3 instance was installed by the
one click installer from EDB and the 9.4 from Bitami, is that correct?

Correct.


So do you know where the 9.4 binaries are installed?

If by binaries, you mean the program files they are installed
C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


Per previous posts you want, whenever possible, to us a newer version of 
pg_dump to move a database from an older version(9.3) to a newer 
one(9.4). Therefore you should do your dump and restore using the 
pg_dump.exe and pg_restore.exe from the Bitanami bin directory. I would 
cd to the above directory and do:


pg_dump -V
pg_restore -V

to make sure the programs are found and are the 9.4 versions.

Then do:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out









--
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] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce > wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump
can't be found there is then a password prompt; I tried the db
password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project"
failed: FATAL:  p
assword authentication failed for user "killian"


note that databases don't have passwords, database USERS have
passwords.  'killian' probably doesn't have a database user, and
since you didn't specify a user, it defaulted to your system
username (expecting that user to have been created in postgres, and
wanting that probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
-U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014  08:35   381,952 pg_dump.exe

09/06/2014  08:35   180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument



Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out




if/when it prompts for a password, thats the password of the
'postgres' database user, as configured in the postgres servers.

note it will prompt for the password a couple times,  once for
postgres on port 5432, and again for postgres on port 5532, at least
if both database services are configured to require passwords for
local connections.


--
john r pierce, recycling bits in santa cruz





--
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 14:56, Adrian Klaver 
wrote:

> On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 11:36, John R Pierce > > wrote:
>>
>> On 12/23/2015 2:25 AM, Killian Driscoll wrote:
>>
>>> Sorry, forgot to add: once I get the warning that the Pg_dump
>>> can't be found there is then a password prompt; I tried the db
>>> password and the pc password but both fail:
>>>
>>> Password:
>>> pg_dump: [archiver (db)] connection to database "irll_project"
>>> failed: FATAL:  p
>>> assword authentication failed for user "killian"
>>>
>>
>> note that databases don't have passwords, database USERS have
>> passwords.  'killian' probably doesn't have a database user, and
>> since you didn't specify a user, it defaulted to your system
>> username (expecting that user to have been created in postgres, and
>> wanting that probably non-existant postgres users passsword)
>>
>> so, ok, try the command with -U postgres, as *
>> *
>>
>> *pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
>> -U postgres -p 5532*
>>
>> OK - I did the dir and it shows that the dump and restore.exe are there,
>> but running the above gives the below errors
>>
>> 09/06/2014  08:35   381,952 pg_dump.exe
>>
>> 09/06/2014  08:35   180,224 pg_restore.exe
>>
>> C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%
>>
>> C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
>> pg_restore -U po
>> stgres -p 5532
>> ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
>> execute
>>
>> pg_restore: [archiver] did not find magic string in file header
>> pg_dump: [custom archiver] could not write to output file: Invalid
>> argument
>>
>
>
> Try breaking the above down into two steps:
>
> pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project
>

Doing this step I get response
could not find a "pg_dump" to execute

>
> pg_restore -U postgres -p 5532 irll_project.out
>
>
>
>>
>> if/when it prompts for a password, thats the password of the
>> 'postgres' database user, as configured in the postgres servers.
>>
>> note it will prompt for the password a couple times,  once for
>> postgres on port 5432, and again for postgres on port 5532, at least
>> if both database services are configured to require passwords for
>> local connections.
>>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

We had that already upthread.

Did you set the path to the bin dir of PostgreSQL as of previous posts?

 

Regards

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 15:14
To: Adrian Klaver 
Cc: John R Pierce ; pgsql-general 

Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 14:56, Adrian Klaver  > wrote:

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce  
 >> wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump
can't be found there is then a password prompt; I tried the db
password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project"
failed: FATAL:  p
assword authentication failed for user "killian"


note that databases don't have passwords, database USERS have
passwords.  'killian' probably doesn't have a database user, and
since you didn't specify a user, it defaulted to your system
username (expecting that user to have been created in postgres, and
wanting that probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
-U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014  08:35   381,952 pg_dump.exe

09/06/2014  08:35   180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument



Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

 

Doing this step I get response

could not find a "pg_dump" to execute 


pg_restore -U postgres -p 5532 irll_project.out

 



if/when it prompts for a password, thats the password of the
'postgres' database user, as configured in the postgres servers.

note it will prompt for the password a couple times,  once for
postgres on port 5432, and again for postgres on port 5532, at least
if both database services are configured to require passwords for
local connections.


--
john r pierce, recycling bits in santa cruz



 

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

 

Re: [GENERAL] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 06:13 AM, Killian Driscoll wrote:

On 23 December 2015 at 14:56, Adrian Klaver > wrote:

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce 
>> wrote:

 On 12/23/2015 2:25 AM, Killian Driscoll wrote:

 Sorry, forgot to add: once I get the warning that the
Pg_dump
 can't be found there is then a password prompt; I tried
the db
 password and the pc password but both fail:

 Password:
 pg_dump: [archiver (db)] connection to database
"irll_project"
 failed: FATAL:  p
 assword authentication failed for user "killian"


 note that databases don't have passwords, database USERS have
 passwords.  'killian' probably doesn't have a database
user, and
 since you didn't specify a user, it defaulted to your system
 username (expecting that user to have been created in
postgres, and
 wanting that probably non-existant postgres users passsword)

 so, ok, try the command with -U postgres, as *
 *

 *pg_dump -Fc -p 5432 **-U postgres **irll_project |
pg_restore
 -U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe
are there,
but running the above gives the below errors

09/06/2014  08:35   381,952 pg_dump.exe

09/06/2014  08:35   180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a
"pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file:
Invalid argument



Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project


Doing this step I get response
could not find a "pg_dump" to execute


So cd into:

C:\Program Files\PostgreSQL\9.3\bin

and try:

pg_dump --help

that will at least establish that the command is being found.




pg_restore -U postgres -p 5532 irll_project.out




 if/when it prompts for a password, thats the password of the
 'postgres' database user, as configured in the postgres
servers.

 note it will prompt for the password a couple times,  once for
 postgres on port 5432, and again for postgres on port 5532,
at least
 if both database services are configured to require
passwords for
 local connections.


 --
 john r pierce, recycling bits in santa cruz




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





--
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] Huge delay to finish even having all the records inserted

[Alexander Franca Fernandes]

Hi,

I'm inserting 14 millions records from a text file using the Postgre COPY
command.

After waiting five days I've realized that all the records seems to be
already inserted,

but the process is still running!!

I don't know if there's any internal postgresql process that impose that I
have to wait even having the records shown in a SELECT query...

I don't know if it's safe to cancel the process right now.

Here's the activity query result (I've cut the columns into lines):

postgres=# SELECT * from pg_stat_activity ;


 datid  |
+
 136776 |
-
 datname  |
--+
 xyzdb|

-
 procpid |
-+-
  303311 |

-
usesysid |
-+-
   16387 |

-
usename  |
-+
xyz  |

-

  current_query   |
--+-
|

--

waiting |
+
f   |

-
  xact_start   |
---+
   |

---
  query_start  |
---+
 2015-12-18 12:01:05.553534-06 |

---
 backend_start | client_addr | client_port
---+-+-
 2015-12-18 12:01:05.547759-06 | |  -1


[]s
Alex

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 15:47, Adrian Klaver 
wrote:

> On 12/23/2015 06:35 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 15:30, Adrian Klaver > > wrote:
>>
>> On 12/23/2015 06:13 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 14:56, Adrian Klaver
>> 
>> >
>> >> wrote:
>>
>>  On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>>
>>  On 23 December 2015 at 11:36, John R Pierce
>> 
>>  > >>
>>  >  > >
>>   On 12/23/2015 2:25 AM, Killian Driscoll wrote:
>>
>>   Sorry, forgot to add: once I get the warning
>> that the
>>  Pg_dump
>>   can't be found there is then a password
>> prompt; I tried
>>  the db
>>   password and the pc password but both fail:
>>
>>   Password:
>>   pg_dump: [archiver (db)] connection to database
>>  "irll_project"
>>   failed: FATAL:  p
>>   assword authentication failed for user "killian"
>>
>>
>>   note that databases don't have passwords, database
>> USERS have
>>   passwords.  'killian' probably doesn't have a
>> database
>>  user, and
>>   since you didn't specify a user, it defaulted to
>> your system
>>   username (expecting that user to have been created
>> in
>>  postgres, and
>>   wanting that probably non-existant postgres users
>> passsword)
>>
>>   so, ok, try the command with -U postgres, as *
>>   *
>>
>>   *pg_dump -Fc -p 5432 **-U postgres
>> **irll_project |
>>  pg_restore
>>   -U postgres -p 5532*
>>
>>  OK - I did the dir and it shows that the dump and
>> restore.exe
>>  are there,
>>  but running the above gives the below errors
>>
>>  09/06/2014  08:35   381,952 pg_dump.exe
>>
>>  09/06/2014  08:35   180,224 pg_restore.exe
>>
>>  C:\Users\killian>path "C:\Program
>> Files\PostgreSQL\9.3\bin";%path%
>>
>>  C:\Users\killian>pg_dump -Fc -p 5432 -U postgres
>> irll_project |
>>  pg_restore -U po
>>  stgres -p 5532
>>  ccoulould not findd a n "pg_restore" to executeot find a
>>  "pg_dump" to
>>  execute
>>
>>  pg_restore: [archiver] did not find magic string in
>> file header
>>  pg_dump: [custom archiver] could not write to output
>> file:
>>  Invalid argument
>>
>>
>>
>>  Try breaking the above down into two steps:
>>
>>  pg_dump -Fc -p 5432 -U postgres -f irll_project.out
>> irll_project
>>
>>
>> Doing this step I get response
>> could not find a "pg_dump" to execute
>>
>>
>> So cd into:
>>
>> C:\Program Files\PostgreSQL\9.3\bin
>>
>> and try:
>>
>> pg_dump --help
>>
>> that will at least establish that the command is being found.
>>
>>
>> OK - --help on the 9.3 lists help options
>>
>
> In your original post you said you have a 9.3 instance and a 9.4 instance.
>
> From your post I would say the 9.3 instance was installed by the one click
> installer from EDB and the 9.4 from Bitami, is that correct?
>
Correct.

>
> So do you know where the 9.4 binaries are installed?
>
If by binaries, you mean the program files they are installed
C:\Bitnami\wappstack-5.5.30-0\postgresql\bin

>
>
>>
>>
>>
>>  pg_restore -U postgres -p 5532 irll_project.out
>>
>>
>>
>>
>>   if/when it prompts for a password, thats the
>> password of the
>>   'postgres' database user, as configured in the
>> postgres
>>  servers.
>>
>>   note it will prompt for the password a couple
>> times,  once for
>>   postgres on port 5432, and again for postgres on
>> port 5532,
>>  at least
>>   if both database services are configured to require
>>  passwords for
>>

Re: [GENERAL] Transfer db from one port to another

[Alban Hertroys]

> On 23 Dec 2015, at 12:43, Killian Driscoll  wrote:
> 
> OK - I did the dir and it shows that the dump and restore.exe are there, but 
> running the above gives the below errors
> 
> 09/06/2014  08:35   381,952 pg_dump.exe
> 
> 09/06/2014  08:35   180,224 pg_restore.exe
> 
> C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%
> 
> C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project | pg_restore -U 
> po
> stgres -p 5532
> ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to execute

Aren't you trying to move a database to PG 9.4? Then you need to use the 
pg_dump and pg_restore utilities of the 9.4 installation, not those of the 9.3 
one. Those utilities are guaranteed to be backwards compatible, but they're not 
necessarily forwards compatible.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
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] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 06:35 AM, Killian Driscoll wrote:

On 23 December 2015 at 15:30, Adrian Klaver > wrote:

On 12/23/2015 06:13 AM, Killian Driscoll wrote:

On 23 December 2015 at 14:56, Adrian Klaver

>> wrote:

 On 12/23/2015 03:43 AM, Killian Driscoll wrote:

 On 23 December 2015 at 11:36, John R Pierce

 >
  path "C:\Program
Files\PostgreSQL\9.3\bin";%path%

 C:\Users\killian>pg_dump -Fc -p 5432 -U postgres
irll_project |
 pg_restore -U po
 stgres -p 5532
 ccoulould not findd a n "pg_restore" to executeot find a
 "pg_dump" to
 execute

 pg_restore: [archiver] did not find magic string in
file header
 pg_dump: [custom archiver] could not write to output file:
 Invalid argument



 Try breaking the above down into two steps:

 pg_dump -Fc -p 5432 -U postgres -f irll_project.out
irll_project


Doing this step I get response
could not find a "pg_dump" to execute


So cd into:

C:\Program Files\PostgreSQL\9.3\bin

and try:

pg_dump --help

that will at least establish that the command is being found.


OK - --help on the 9.3 lists help options


In your original post you said you have a 9.3 instance and a 9.4 instance.

From your post I would say the 9.3 instance was installed by the one 
click installer from EDB and the 9.4 from Bitami, is that correct?


So do you know where the 9.4 binaries are installed?






 pg_restore -U postgres -p 5532 irll_project.out




  if/when it prompts for a password, thats the
password of the
  'postgres' database user, as configured in the
postgres
 servers.

  note it will prompt for the password a couple
times,  once for
  postgres on port 5432, and again for postgres on
port 5532,
 at least
  if both database services are configured to require
 passwords for
  local connections.


  --
  john r pierce, recycling bits in santa cruz




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




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





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your 

Re: [GENERAL] Shared system resources

[oleg yusim]

HI George,

Thanks, this information clears the situation. Now, question to you and
David.

May we run into situation, when attacker dumps memory and analyses it for
valuable content, instead of reserving it for own process, where it would
be zeroed? My understanding, it is a possibility. Does kernel have any
safeguard against it?

Thanks,

Oleg

On Wed, Dec 23, 2015 at 2:13 AM, George Neuner  wrote:

> On Tue, 22 Dec 2015 23:21:27 +, David Wilson 
> wrote:
>
> >On Linux the memory pages of an exiting process aren't sanitized at
> >exit, however it is impossible(?) for userspace to reallocate them
> >without the kernel first zeroing their contents.
>
> Not impossible, but it requires a non-standard kernel.
>
> Since 2.6.33, mmap() accepts the flag MAP_UNINITIALIZED which allows
> pages to be mapped without being cleared.  The flag has no effect
> unless the kernel was built with CONFIG_MMAP_ALLOW_UNINITIALIZED.
>
>
> No mainstream distro enables this.  AFAIK, there is NO distro at all
> that enables it ... it's too big a security risk for a general purpose
> system.  It's intended to support embedded systems where the set of
> programs is known.
>
> George
>
>
>
> --
> 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] Shared system resources

[oleg yusim]

Jim,

Help me out with this statement:

"There's tons of raw data stored in the shared memory segment, and some of
that can be copied to process local memory at any time. If they OS doesn't
secure that adequately there's certainly nothing that Postgres or any other
database can do about it."

To my knowledge, many databases are using what called TDE to encrypt data
at rest and protect data from being accessed by attacker on host this way.
Here is the reference to quick guide on it:
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

Now, when you are saying " tons of raw data stored in the shared memory
segment, and some of that can be copied to process local memory at any time"
what kind of memory you are referring too? Is it that files, which
generally end up being protected with TDE, or is it a buffer memory, which
get's used by database processes, but doesn't belong to database permanent
storage?

Can you give me more details here, so I would understand the actual mapping
and scale of the issue?

Thanks,

Oleg

On Wed, Dec 23, 2015 at 9:55 AM, Jim Nasby  wrote:

> On 12/23/15 7:55 AM, oleg yusim wrote:
>
>> Sure David. For simplicity of modeling here, let's assume raw database
>> data was encrypted and the only possibility for attacker to get
>> something from raw data is to go and dig into sessions leftovers. Now,
>> with that has been said, do you happen to know what information actually
>> gets stored during the session into memory, reserved by session process?
>> I'm trying to determine, basically, does it even worth a talk - maybe
>> there is nothing at all valuable.
>>
>
> There's tons of raw data stored in the shared memory segment, and some of
> that can be copied to process local memory at any time. If they OS doesn't
> secure that adequately there's certainly nothing that Postgres or any other
> database can do about it.
>
> As David said, by the time you're concerned about someone getting access
> to raw memory it's already way too late.
>
> As for memory pages being zero'd after they are returned to the OS, that's
> entirely up to the OS. The only thing you could do on the Postgres side is
> to compile with memory debugging enabled, which will over-write any memory
> that's freed with a magic value. That's done to help hunt down memory
> access bugs, but would have the obvious side effect of obliterating any
> data that was in the page.
>
> Uh, only thing is, I don't know if this is done if we're going to be
> returning the memory to the OS.
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] Shared system resources

[oleg yusim]

Sure David. For simplicity of modeling here, let's assume raw database data
was encrypted and the only possibility for attacker to get something from
raw data is to go and dig into sessions leftovers. Now, with that has been
said, do you happen to know what information actually gets stored during
the session into memory, reserved by session process? I'm trying to
determine, basically, does it even worth a talk - maybe there is nothing at
all valuable.

Thanks,

Oleg

On Wed, Dec 23, 2015 at 7:41 AM, David Wilson  wrote:

> On Wed, Dec 23, 2015 at 07:07:31AM -0600, oleg yusim wrote:
>
> > May we run into situation, when attacker dumps memory and analyses it
> > for valuable content, instead of reserving it for own process, where
> > it would be zeroed? My understanding, it is a possibility. Does kernel
> > have any safeguard against it?
>
> Sure it might be possible, but they would not have much useful
> information about which old processes the pages belonged to, and
> besides, they could most likely simply dump memory of a connected client
> in this case, or indeed just examine the filesystem or cache to get at
> the raw PG database files.
>
> Once someone has this level of access to the system it's not really
> useful to model threats much further.
>
> One minor correction from my first mail: MAP_UNINITIALIZED is indeed
> accessible to non-root, but as George mentions only when a non-default
> kernel parameter has been enabled.
>
>
> David
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:19, Melvin Davidson  wrote:

> It's possible the restore is still building indexes.
>
> What does it show when you run this query?
>
Where do I run this query? Do I stop the restore that is 'active'?

>
> SELECT datname,
>pid as pid,
>client_addr,
>usename as user,
>query,
>CASE WHEN waiting = TRUE
> THEN 'BLOCKED'
> ELSE 'no'
> END as waiting,
>query_start,
>current_timestamp - query_start as duration
>   FROM pg_stat_activity
>  WHERE pg_backend_pid() <> pid
> ORDER BY datname,
>  query_start;
>
>
> On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver 
> wrote:
>
>> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>>
>>> On 23 December 2015 at 20:07, Adrian Klaver >> > wrote:
>>>
>>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>>
>>> On 23 December 2015 at 16:02, Adrian Klaver
>>> 
>>> >> >> wrote:
>>>
>>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>>
>>>  On 23 December 2015 at 15:47, Adrian Klaver
>>>  >> 
>>> >>
>>> >
>>>
>>>
>>>
>>>
>>>So cd into:
>>>
>>>C:\Program Files\PostgreSQL\9.3\bin
>>>
>>>and try:
>>>
>>>pg_dump --help
>>>
>>>that will at least establish that the
>>> command is
>>>  being found.
>>>
>>>
>>>   OK - --help on the 9.3 lists help options
>>>
>>>
>>>   In your original post you said you have a 9.3
>>> instance and
>>>  a 9.4
>>>   instance.
>>>
>>>>From your post I would say the 9.3 instance was
>>> installed
>>>  by the
>>>   one click installer from EDB and the 9.4 from
>>> Bitami, is
>>>  that correct?
>>>
>>>  Correct.
>>>
>>>
>>>   So do you know where the 9.4 binaries are
>>> installed?
>>>
>>>  If by binaries, you mean the program files they are
>>> installed
>>>  C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>>>
>>>
>>>  Per previous posts you want, whenever possible, to us a
>>> newer
>>>  version of pg_dump to move a database from an older
>>> version(9.3) to
>>>  a newer one(9.4). Therefore you should do your dump and
>>> restore
>>>  using the pg_dump.exe and pg_restore.exe from the Bitanami
>>> bin
>>>  directory. I would cd to the above directory and do:
>>>
>>>  pg_dump -V
>>>  pg_restore -V
>>>
>>>  to make sure the programs are found and are the 9.4
>>> versions.
>>>
>>>  Then do:
>>>
>>>  pg_dump -Fc -p 5432 -U postgres -f irll_project.out
>>> irll_project
>>>
>>>  pg_restore -U postgres -p 5532 irll_project.out
>>>
>>>
>>> It appeared to work with this method, but it has now been
>>> running for
>>> almost 4 hours with no result. The db is not that large
>>> (probably v.
>>> small by most standards) and the .out file is ~200mb
>>>
>>>
>>> What is running, the dump or the restore?
>>>
>>> The restore - I can see the dump .out file that was created at 16hr in
>>> the postgresql/bin folder
>>>
>>
>> So how are you determining it is running and that it is not doing
>> anything?
>>
>> What does the Postgres log for the 9.4 instance show?
>>
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>  --
>>>  Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> 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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 11:03 AM, Killian Driscoll wrote:

On 23 December 2015 at 16:02, Adrian Klaver > wrote:

On 12/23/2015 06:50 AM, Killian Driscoll wrote:

On 23 December 2015 at 15:47, Adrian Klaver




  So cd into:

  C:\Program Files\PostgreSQL\9.3\bin

  and try:

  pg_dump --help

  that will at least establish that the command is
being found.


 OK - --help on the 9.3 lists help options


 In your original post you said you have a 9.3 instance and
a 9.4
 instance.

  >From your post I would say the 9.3 instance was installed
by the
 one click installer from EDB and the 9.4 from Bitami, is
that correct?

Correct.


 So do you know where the 9.4 binaries are installed?

If by binaries, you mean the program files they are installed
C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


Per previous posts you want, whenever possible, to us a newer
version of pg_dump to move a database from an older version(9.3) to
a newer one(9.4). Therefore you should do your dump and restore
using the pg_dump.exe and pg_restore.exe from the Bitanami bin
directory. I would cd to the above directory and do:

pg_dump -V
pg_restore -V

to make sure the programs are found and are the 9.4 versions.

Then do:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out


It appeared to work with this method, but it has now been running for
almost 4 hours with no result. The db is not that large (probably v.
small by most standards) and the .out file is ~200mb


What is running, the dump or the restore?









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





--
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] Transfer db from one port to another

[Melvin Davidson]

It's possible the restore is still building indexes.

What does it show when you run this query?

SELECT datname,
   pid as pid,
   client_addr,
   usename as user,
   query,
   CASE WHEN waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
   query_start,
   current_timestamp - query_start as duration
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY datname,
 query_start;


On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver 
wrote:

> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:07, Adrian Klaver > > wrote:
>>
>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 16:02, Adrian Klaver
>> 
>> > >> wrote:
>>
>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>
>>  On 23 December 2015 at 15:47, Adrian Klaver
>>  > 
>> >
>> >
>>
>>
>>
>>
>>So cd into:
>>
>>C:\Program Files\PostgreSQL\9.3\bin
>>
>>and try:
>>
>>pg_dump --help
>>
>>that will at least establish that the
>> command is
>>  being found.
>>
>>
>>   OK - --help on the 9.3 lists help options
>>
>>
>>   In your original post you said you have a 9.3
>> instance and
>>  a 9.4
>>   instance.
>>
>>>From your post I would say the 9.3 instance was
>> installed
>>  by the
>>   one click installer from EDB and the 9.4 from
>> Bitami, is
>>  that correct?
>>
>>  Correct.
>>
>>
>>   So do you know where the 9.4 binaries are installed?
>>
>>  If by binaries, you mean the program files they are
>> installed
>>  C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>>
>>
>>  Per previous posts you want, whenever possible, to us a newer
>>  version of pg_dump to move a database from an older
>> version(9.3) to
>>  a newer one(9.4). Therefore you should do your dump and
>> restore
>>  using the pg_dump.exe and pg_restore.exe from the Bitanami
>> bin
>>  directory. I would cd to the above directory and do:
>>
>>  pg_dump -V
>>  pg_restore -V
>>
>>  to make sure the programs are found and are the 9.4 versions.
>>
>>  Then do:
>>
>>  pg_dump -Fc -p 5432 -U postgres -f irll_project.out
>> irll_project
>>
>>  pg_restore -U postgres -p 5532 irll_project.out
>>
>>
>> It appeared to work with this method, but it has now been
>> running for
>> almost 4 hours with no result. The db is not that large (probably
>> v.
>> small by most standards) and the .out file is ~200mb
>>
>>
>> What is running, the dump or the restore?
>>
>> The restore - I can see the dump .out file that was created at 16hr in
>> the postgresql/bin folder
>>
>
> So how are you determining it is running and that it is not doing anything?
>
> What does the Postgres log for the 9.4 instance show?
>
>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>  --
>>  Adrian Klaver
>> adrian.kla...@aklaver.com 
>> > >
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> 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
>



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

Re: [GENERAL] Shared system resources

[oleg yusim]

John,

To my knowledge, TDE is employed not only by Microsoft, but by Oracle too.
I recall it also has a mechanism of protecting keys. Here are references:

https://msdn.microsoft.com/en-us/library/bb934049.aspx
http://www.oracle.com/technetwork/database/options/advanced-security/index-099011.html

Thank you very much for that piece:

"In PostgreSQL 'shared memory' has a quite specific meaning, its referring
to the pool of buffer memory (ram) shared by all postgres server
processes.   this is primarily used as the buffer cache. In a properly
secured operating system, ONLY the postgres server processes have access to
this shared memory segment"

It helped me to understand terminology used by other reponders better.

Thanks,

Oleg

On Wed, Dec 23, 2015 at 10:48 AM, John R Pierce  wrote:

> On 12/23/2015 8:16 AM, oleg yusim wrote:
>
>>
>> To my knowledge, many databases are using what called TDE to encrypt data
>> at rest and protect data from being accessed by attacker on host this way.
>> Here is the reference to quick guide on it:
>> https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/
>>
>
> that article is talking about a specific feature of Microsoft SQL Server
> Enterprise Edition, which upon a quick skim sounds to me to be smoke and
> mirrors 'security-by-checklist' protection.   If the encryption keys are
> stored on the system, then anyone with access to the raw data can decrypt
> it, no matter how much smoke and mirrors you wave around to obfuscate this
> fact.
>
> In PostgreSQL 'shared memory' has a quite specific meaning, its referring
> to the pool of buffer memory (ram) shared by all postgres server
> processes.   this is primarily used as the buffer cache. In a properly
> secured operating system, ONLY the postgres server processes have access to
> this shared memory segment, but the details of OS level memory management
> are outide postgres's scope, since its portable and designed to be able to
> run on most any OS that provides basic memory management, multiple
> processes, and a reliable/robust file system, with tcp/ip socket support.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:26, Melvin Davidson  wrote:

> Do not stop the active restore.
> Just run psql from the command shell in the Bitnami binary directory and
> use -U postgres and -p 5532 flags.
>
>
> On Wed, Dec 23, 2015 at 2:22 PM, Killian Driscoll <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:19, Melvin Davidson 
>> wrote:
>>
>>> It's possible the restore is still building indexes.
>>>
>>> What does it show when you run this query?
>>>
>> Where do I run this query? Do I stop the restore that is 'active'?
>>
>>>
>>> SELECT datname,
>>>pid as pid,
>>>client_addr,
>>>usename as user,
>>>query,
>>>CASE WHEN waiting = TRUE
>>> THEN 'BLOCKED'
>>> ELSE 'no'
>>> END as waiting,
>>>query_start,
>>>current_timestamp - query_start as duration
>>>   FROM pg_stat_activity
>>>  WHERE pg_backend_pid() <> pid
>>> ORDER BY datname,
>>>  query_start;
>>>
>>
OK - I get this
Server [localhost]:
Database [postgres]:
Port [5432]: 5532
Username [postgres]:
psql (9.3.4, server 9.4.4)
WARNING: psql major version 9.3, server major version 9.4.
 Some psql features might not work.
WARNING: Console code page (850) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.
Type "help" for help.

postgres=# SELECT datname,
postgres-#pid as pid,
postgres-#client_addr,
postgres-#usename as user,
postgres-#query,
postgres-#CASE WHEN waiting = TRUE
postgres-# THEN 'BLOCKED'
postgres-# ELSE 'no'
postgres-# END as waiting,
postgres-#query_start,
postgres-#current_timestamp - query_start as duration
postgres-#   FROM pg_stat_activity
postgres-#  WHERE pg_backend_pid() <> pid
postgres-# ORDER BY datname,
postgres-#  query_start;
   datname| pid  | client_addr |   user   |
  query
   | waiting |query_start |   duration
--+--+-+--+-

---+-++--
 irll_project | 8088 | 127.0.0.1   | postgres | SELECT defaclacl FROM
pg_catalog
.pg_default_acl dacl WHERE dacl.defaclnamespace = 19228::oid AND
defaclobjtype='
T' | no  | 2015-12-23 17:37:18.295+01 | 02:46:37.17
 postgres | 5084 | 127.0.0.1   | postgres | SELECT setting FROM
pg_settings
WHERE name IN ('autovacuum', 'track_counts')
   | no  | 2015-12-23 17:37:02.469+01 | 02:46:52.996
(2 rows)


postgres=#

>
>>>
>>> On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>>
 On 12/23/2015 11:09 AM, Killian Driscoll wrote:

> On 23 December 2015 at 20:07, Adrian Klaver  > wrote:
>
> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>
> On 23 December 2015 at 16:02, Adrian Klaver
> 
>  >> wrote:
>
>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>
>  On 23 December 2015 at 15:47, Adrian Klaver
>   
> 
> >
>
>
>
>
>So cd into:
>
>C:\Program Files\PostgreSQL\9.3\bin
>
>and try:
>
>pg_dump --help
>
>that will at least establish that the
> command is
>  being found.
>
>
>   OK - --help on the 9.3 lists help options
>
>
>   In your original post you said you have a 9.3
> instance and
>  a 9.4
>   instance.
>
>>From your post I would say the 9.3 instance was
> installed
>  by the
>   one click installer from EDB and the 9.4 from
> Bitami, is
>  that correct?
>
>  Correct.
>
>
>   So do you know where the 9.4 binaries are
> installed?
>
>  If by binaries, you mean the program files they are
> installed
>  C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>
>
> 

Re: [GENERAL] Transfer db from one port to another

[Melvin Davidson]

Do not stop the active restore.
Just run psql from the command shell in the Bitnami binary directory and
use -U postgres and -p 5532 flags.


On Wed, Dec 23, 2015 at 2:22 PM, Killian Driscoll  wrote:

> On 23 December 2015 at 20:19, Melvin Davidson 
> wrote:
>
>> It's possible the restore is still building indexes.
>>
>> What does it show when you run this query?
>>
> Where do I run this query? Do I stop the restore that is 'active'?
>
>>
>> SELECT datname,
>>pid as pid,
>>client_addr,
>>usename as user,
>>query,
>>CASE WHEN waiting = TRUE
>> THEN 'BLOCKED'
>> ELSE 'no'
>> END as waiting,
>>query_start,
>>current_timestamp - query_start as duration
>>   FROM pg_stat_activity
>>  WHERE pg_backend_pid() <> pid
>> ORDER BY datname,
>>  query_start;
>>
>>
>> On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver > > wrote:
>>
>>> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>>>
 On 23 December 2015 at 20:07, Adrian Klaver > wrote:

 On 12/23/2015 11:03 AM, Killian Driscoll wrote:

 On 23 December 2015 at 16:02, Adrian Klaver
 
 >> wrote:

  On 12/23/2015 06:50 AM, Killian Driscoll wrote:

  On 23 December 2015 at 15:47, Adrian Klaver
  
 >




So cd into:

C:\Program Files\PostgreSQL\9.3\bin

and try:

pg_dump --help

that will at least establish that the
 command is
  being found.


   OK - --help on the 9.3 lists help options


   In your original post you said you have a 9.3
 instance and
  a 9.4
   instance.

>From your post I would say the 9.3 instance was
 installed
  by the
   one click installer from EDB and the 9.4 from
 Bitami, is
  that correct?

  Correct.


   So do you know where the 9.4 binaries are
 installed?

  If by binaries, you mean the program files they are
 installed
  C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


  Per previous posts you want, whenever possible, to us a
 newer
  version of pg_dump to move a database from an older
 version(9.3) to
  a newer one(9.4). Therefore you should do your dump and
 restore
  using the pg_dump.exe and pg_restore.exe from the Bitanami
 bin
  directory. I would cd to the above directory and do:

  pg_dump -V
  pg_restore -V

  to make sure the programs are found and are the 9.4
 versions.

  Then do:

  pg_dump -Fc -p 5432 -U postgres -f irll_project.out
 irll_project

  pg_restore -U postgres -p 5532 irll_project.out


 It appeared to work with this method, but it has now been
 running for
 almost 4 hours with no result. The db is not that large
 (probably v.
 small by most standards) and the .out file is ~200mb


 What is running, the dump or the restore?

 The restore - I can see the dump .out file that was created at 16hr in
 the postgresql/bin folder

>>>
>>> So how are you determining it is running and that it is not doing
>>> anything?
>>>
>>> What does the Postgres log for the 9.4 instance show?
>>>
>>>









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




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



>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make 

Re: [GENERAL] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 11:27 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:24, Adrian Klaver > wrote:

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:14, Adrian Klaver

>> wrote:





 So how are you determining it is running and that it is not
doing
 anything?

Since I ran the restore the windows shell has been 'active' with
codes
lines running - I took that as being active.!


So what do they say?

If I 'slow down' the code by using the scroll on the right I can see
words from the db I recognise, but the rest is very long alphanumeric
characters.



 What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and
Security\Administrative
Tools in event viewer or elsewhere?


My guess in the Bitnami directory tree under logs/ or something similar.


I can't see a log folder.


Not sure how you installed Bitnami, but here is quick reference showing 
file locations:


https://wiki.bitnami.com/Components/PostgreSQL#Quick_reference_card


--
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] Shared system resources

[John R Pierce]

On 12/23/2015 8:16 AM, oleg yusim wrote:


To my knowledge, many databases are using what called TDE to encrypt 
data at rest and protect data from being accessed by attacker on host 
this way. Here is the reference to quick guide on it: 
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/


that article is talking about a specific feature of Microsoft SQL Server 
Enterprise Edition, which upon a quick skim sounds to me to be smoke and 
mirrors 'security-by-checklist' protection.   If the encryption keys are 
stored on the system, then anyone with access to the raw data can 
decrypt it, no matter how much smoke and mirrors you wave around to 
obfuscate this fact.


In PostgreSQL 'shared memory' has a quite specific meaning, its 
referring to the pool of buffer memory (ram) shared by all postgres 
server processes.   this is primarily used as the buffer cache. In a 
properly secured operating system, ONLY the postgres server processes 
have access to this shared memory segment, but the details of OS level 
memory management are outide postgres's scope, since its portable and 
designed to be able to run on most any OS that provides basic memory 
management, multiple processes, and a reliable/robust file system, with 
tcp/ip socket support.




--
john r pierce, recycling bits in santa cruz



--
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] Shared system resources

[oleg yusim]

Thank you very much George, that is exactly the piece of information I was
missing.

Oleg

On Wed, Dec 23, 2015 at 10:55 AM, George Neuner 
wrote:

> Hi Oleg,
>
> On Wed, 23 Dec 2015 07:07:31 -0600, oleg yusim 
> wrote:
>
> >May we run into situation, when attacker dumps memory and analyses it for
> >valuable content, instead of reserving it for own process, where it would
> >be zeroed? My understanding, it is a possibility. Does kernel have any
> >safeguard against it?
>
> With recent kernels, by default there is no way for a userspace
> process (even root) to dump memory.  Older kernels by default
> permitted a root process unrestricted access to /dev/mem and
> /dev/kmem, however in general that isn't needed and has long been
> disabled by the mahor distros.  [see CONFIG_STRICT_DEVMEM].  IIRC, the
> default setting was changed in 2011.
>
> With sufficient privileges, a debugger-like process can attach and
> examine the memory of a running - or just terminated - process, but it
> won't have access to discarded (unmapped) memory.
>
> The MAP_UNINITIALIZED trick, even if it works, is not a predictable
> attack vector.  There is no way to ask for any *particular* VMM page -
> mmap() just gives you a set of pages sufficient to cover the requested
> address range ... you don't know what process those pages previously
> belonged to.  Obviously there is a known algorithm for satisfying the
> page requests, but the set of free pages includes both code and data
> and depends on the history of system activity.  There's no guarantee
> to get anything useful.
>
> I'm not sure any of this really answers your question.
> George
>
>
>
> --
> 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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:14, Adrian Klaver 
wrote:

> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:07, Adrian Klaver > > wrote:
>>
>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 16:02, Adrian Klaver
>> 
>> > >> wrote:
>>
>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>
>>  On 23 December 2015 at 15:47, Adrian Klaver
>>  > 
>> >
>> >
>>
>>
>>
>>
>>So cd into:
>>
>>C:\Program Files\PostgreSQL\9.3\bin
>>
>>and try:
>>
>>pg_dump --help
>>
>>that will at least establish that the
>> command is
>>  being found.
>>
>>
>>   OK - --help on the 9.3 lists help options
>>
>>
>>   In your original post you said you have a 9.3
>> instance and
>>  a 9.4
>>   instance.
>>
>>>From your post I would say the 9.3 instance was
>> installed
>>  by the
>>   one click installer from EDB and the 9.4 from
>> Bitami, is
>>  that correct?
>>
>>  Correct.
>>
>>
>>   So do you know where the 9.4 binaries are installed?
>>
>>  If by binaries, you mean the program files they are
>> installed
>>  C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>>
>>
>>  Per previous posts you want, whenever possible, to us a newer
>>  version of pg_dump to move a database from an older
>> version(9.3) to
>>  a newer one(9.4). Therefore you should do your dump and
>> restore
>>  using the pg_dump.exe and pg_restore.exe from the Bitanami
>> bin
>>  directory. I would cd to the above directory and do:
>>
>>  pg_dump -V
>>  pg_restore -V
>>
>>  to make sure the programs are found and are the 9.4 versions.
>>
>>  Then do:
>>
>>  pg_dump -Fc -p 5432 -U postgres -f irll_project.out
>> irll_project
>>
>>  pg_restore -U postgres -p 5532 irll_project.out
>>
>>
>> It appeared to work with this method, but it has now been
>> running for
>> almost 4 hours with no result. The db is not that large (probably
>> v.
>> small by most standards) and the .out file is ~200mb
>>
>>
>> What is running, the dump or the restore?
>>
>> The restore - I can see the dump .out file that was created at 16hr in
>> the postgresql/bin folder
>>
>
> So how are you determining it is running and that it is not doing anything?
>
Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.!

>
> What does the Postgres log for the 9.4 instance show?
>
Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?

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

Re: [GENERAL] Shared system resources

[Melvin Davidson]

Oleg,

As others have pointed out, worrying about someone accessing database
shared memory is like worrying about an asteroid striking the earth and
wiping out all life.
It's a one in a billion chance compared to other security violations that
can occur.
You are better off concentrating on proper O/S security and user/table
permissions. That is how to implement database security!

On Wed, Dec 23, 2015 at 12:10 PM, oleg yusim  wrote:

> Thank you very much George, that is exactly the piece of information I was
> missing.
>
> Oleg
>
> On Wed, Dec 23, 2015 at 10:55 AM, George Neuner 
> wrote:
>
>> Hi Oleg,
>>
>> On Wed, 23 Dec 2015 07:07:31 -0600, oleg yusim 
>> wrote:
>>
>> >May we run into situation, when attacker dumps memory and analyses it for
>> >valuable content, instead of reserving it for own process, where it would
>> >be zeroed? My understanding, it is a possibility. Does kernel have any
>> >safeguard against it?
>>
>> With recent kernels, by default there is no way for a userspace
>> process (even root) to dump memory.  Older kernels by default
>> permitted a root process unrestricted access to /dev/mem and
>> /dev/kmem, however in general that isn't needed and has long been
>> disabled by the mahor distros.  [see CONFIG_STRICT_DEVMEM].  IIRC, the
>> default setting was changed in 2011.
>>
>> With sufficient privileges, a debugger-like process can attach and
>> examine the memory of a running - or just terminated - process, but it
>> won't have access to discarded (unmapped) memory.
>>
>> The MAP_UNINITIALIZED trick, even if it works, is not a predictable
>> attack vector.  There is no way to ask for any *particular* VMM page -
>> mmap() just gives you a set of pages sufficient to cover the requested
>> address range ... you don't know what process those pages previously
>> belonged to.  Obviously there is a known algorithm for satisfying the
>> page requests, but the set of free pages includes both code and data
>> and depends on the history of system activity.  There's no guarantee
>> to get anything useful.
>>
>> I'm not sure any of this really answers your question.
>> George
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>


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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 16:02, Adrian Klaver 
wrote:

> On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 15:47, Adrian Klaver >
>
>
>>  So cd into:
>>
>>  C:\Program Files\PostgreSQL\9.3\bin
>>
>>  and try:
>>
>>  pg_dump --help
>>
>>  that will at least establish that the command is being found.
>>
>>
>> OK - --help on the 9.3 lists help options
>>
>>
>> In your original post you said you have a 9.3 instance and a 9.4
>> instance.
>>
>>  >From your post I would say the 9.3 instance was installed by the
>> one click installer from EDB and the 9.4 from Bitami, is that correct?
>>
>> Correct.
>>
>>
>> So do you know where the 9.4 binaries are installed?
>>
>> If by binaries, you mean the program files they are installed
>> C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>>
>
> Per previous posts you want, whenever possible, to us a newer version of
> pg_dump to move a database from an older version(9.3) to a newer one(9.4).
> Therefore you should do your dump and restore using the pg_dump.exe and
> pg_restore.exe from the Bitanami bin directory. I would cd to the above
> directory and do:
>
> pg_dump -V
> pg_restore -V
>
> to make sure the programs are found and are the 9.4 versions.
>
> Then do:
>
> pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project
>
> pg_restore -U postgres -p 5532 irll_project.out
>

It appeared to work with this method, but it has now been running for
almost 4 hours with no result. The db is not that large (probably v. small
by most standards) and the .out file is ~200mb

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

Re: [GENERAL] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 11:09 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:07, Adrian Klaver > wrote:

On 12/23/2015 11:03 AM, Killian Driscoll wrote:

On 23 December 2015 at 16:02, Adrian Klaver

>> wrote:

 On 12/23/2015 06:50 AM, Killian Driscoll wrote:

 On 23 December 2015 at 15:47, Adrian Klaver
 
>




   So cd into:

   C:\Program Files\PostgreSQL\9.3\bin

   and try:

   pg_dump --help

   that will at least establish that the
command is
 being found.


  OK - --help on the 9.3 lists help options


  In your original post you said you have a 9.3
instance and
 a 9.4
  instance.

   >From your post I would say the 9.3 instance was
installed
 by the
  one click installer from EDB and the 9.4 from
Bitami, is
 that correct?

 Correct.


  So do you know where the 9.4 binaries are installed?

 If by binaries, you mean the program files they are
installed
 C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


 Per previous posts you want, whenever possible, to us a newer
 version of pg_dump to move a database from an older
version(9.3) to
 a newer one(9.4). Therefore you should do your dump and restore
 using the pg_dump.exe and pg_restore.exe from the Bitanami bin
 directory. I would cd to the above directory and do:

 pg_dump -V
 pg_restore -V

 to make sure the programs are found and are the 9.4 versions.

 Then do:

 pg_dump -Fc -p 5432 -U postgres -f irll_project.out
irll_project

 pg_restore -U postgres -p 5532 irll_project.out


It appeared to work with this method, but it has now been
running for
almost 4 hours with no result. The db is not that large (probably v.
small by most standards) and the .out file is ~200mb


What is running, the dump or the restore?

The restore - I can see the dump .out file that was created at 16hr in
the postgresql/bin folder


So how are you determining it is running and that it is not doing anything?

What does the Postgres log for the 9.4 instance show?












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




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





--
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] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:14, Adrian Klaver > wrote:






So how are you determining it is running and that it is not doing
anything?

Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.!


So what do they say?




What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?


My guess in the Bitnami directory tree under logs/ or something similar.














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




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




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





--
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:24, Adrian Klaver 
wrote:

> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:14, Adrian Klaver > > wrote:
>>
>
>
>
>>
>> So how are you determining it is running and that it is not doing
>> anything?
>>
>> Since I ran the restore the windows shell has been 'active' with codes
>> lines running - I took that as being active.!
>>
>
> So what do they say?
>
> If I 'slow down' the code by using the scroll on the right I can see words
from the db I recognise, but the rest is very long alphanumeric characters.

>
>>
>> What does the Postgres log for the 9.4 instance show?
>>
>> Where is the log - here Control Panel\System and Security\Administrative
>> Tools in event viewer or elsewhere?
>>
>
> My guess in the Bitnami directory tree under logs/ or something similar.
>

I can't see a log folder.

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

Re: [GENERAL] Shared system resources

[George Neuner]

Hi Oleg,

On Wed, 23 Dec 2015 07:07:31 -0600, oleg yusim 
wrote:

>May we run into situation, when attacker dumps memory and analyses it for
>valuable content, instead of reserving it for own process, where it would
>be zeroed? My understanding, it is a possibility. Does kernel have any
>safeguard against it?

With recent kernels, by default there is no way for a userspace
process (even root) to dump memory.  Older kernels by default
permitted a root process unrestricted access to /dev/mem and
/dev/kmem, however in general that isn't needed and has long been
disabled by the mahor distros.  [see CONFIG_STRICT_DEVMEM].  IIRC, the
default setting was changed in 2011. 

With sufficient privileges, a debugger-like process can attach and
examine the memory of a running - or just terminated - process, but it
won't have access to discarded (unmapped) memory.

The MAP_UNINITIALIZED trick, even if it works, is not a predictable
attack vector.  There is no way to ask for any *particular* VMM page -
mmap() just gives you a set of pages sufficient to cover the requested
address range ... you don't know what process those pages previously
belonged to.  Obviously there is a known algorithm for satisfying the
page requests, but the set of free pages includes both code and data
and depends on the history of system activity.  There's no guarantee
to get anything useful.

I'm not sure any of this really answers your question.
George



-- 
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:07, Adrian Klaver 
wrote:

> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 16:02, Adrian Klaver > > wrote:
>>
>> On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 15:47, Adrian Klaver
>> 
>>
>>
>>
>>
>>   So cd into:
>>
>>   C:\Program Files\PostgreSQL\9.3\bin
>>
>>   and try:
>>
>>   pg_dump --help
>>
>>   that will at least establish that the command is
>> being found.
>>
>>
>>  OK - --help on the 9.3 lists help options
>>
>>
>>  In your original post you said you have a 9.3 instance and
>> a 9.4
>>  instance.
>>
>>   >From your post I would say the 9.3 instance was installed
>> by the
>>  one click installer from EDB and the 9.4 from Bitami, is
>> that correct?
>>
>> Correct.
>>
>>
>>  So do you know where the 9.4 binaries are installed?
>>
>> If by binaries, you mean the program files they are installed
>> C:\Bitnami\wappstack-5.5.30-0\postgresql\bin
>>
>>
>> Per previous posts you want, whenever possible, to us a newer
>> version of pg_dump to move a database from an older version(9.3) to
>> a newer one(9.4). Therefore you should do your dump and restore
>> using the pg_dump.exe and pg_restore.exe from the Bitanami bin
>> directory. I would cd to the above directory and do:
>>
>> pg_dump -V
>> pg_restore -V
>>
>> to make sure the programs are found and are the 9.4 versions.
>>
>> Then do:
>>
>> pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project
>>
>> pg_restore -U postgres -p 5532 irll_project.out
>>
>>
>> It appeared to work with this method, but it has now been running for
>> almost 4 hours with no result. The db is not that large (probably v.
>> small by most standards) and the .out file is ~200mb
>>
>
> What is running, the dump or the restore?

The restore - I can see the dump .out file that was created at 16hr in the
postgresql/bin folder

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

Re: [GENERAL] Secret Santa List

[Lou Duchez]

Why not generate the required results in a SELECT then update from 
that. row_number() could allow you to generate a random number to each 
giver, then we can generate another random number and join to each 
random number. That'll give you a giver and recipient combination.


e.g:

select giver,recipient from
(select row_number() over (order by random()) rn, giver from 
secretsanta) g

inner join
(select row_number() over (order by random()) rn, giver recipient from 
secretsanta) r on g.rn = r.rn


You can then wrap that up in a CTE, something along the lines of:

with cte (giver, recipient) as (
select giver,recipient from
(select row_number() over (order by random()) rn, giver from 
secretsanta) g

inner join
(select row_number() over (order by random()) rn, giver recipient from 
secretsanta) r on g.rn = r.rn

)
update secretsanta set recipient = cte.recipient from cte WHERE 
cte.giver = secretsanta.giver;



Hey, I think that works!  Thanks!




--
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] Huge delay to finish even having all the records inserted

[Alexander Franca Fernandes]

Yep, you're right!

Thank you for your feedback!

[]s
Alex

On Wed, Dec 23, 2015 at 1:14 PM, Adrian Klaver 
wrote:

> On 12/23/2015 06:49 AM, Alexander Franca Fernandes wrote:
>
>> Hi,
>>
>> I'm inserting 14 millions records from a text file using the Postgre
>> COPY command.
>>
>
> So how did you execute this?
>
> In other words what is the script you used?
>
>
>> After waiting five days I've realized that all the records seems to be
>> already inserted,
>>
>> but the process is still running!!
>>
>
> From what I see it is not running:
>
>
> http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>
> idle: The backend is waiting for a new client command.
>
>
> Looks to me whatever you used to run the COPY connected and then never
> disconnected and Postgres is waiting for more instructions on that
> connection.
>
>
>
>> I don't know if there's any internal postgresql process that impose that
>> I have to wait even having the records shown in a SELECT query...
>>
>> I don't know if it's safe to cancel the process right now.
>>
>> Here's the activity query result (I've cut the columns into lines):
>>
>> postgres=# SELECT * from pg_stat_activity ;
>>
>>
>>   datid  |
>> +
>>   136776 |
>> -
>>   datname  |
>> --+
>>   xyzdb|
>>
>> -
>>   procpid |
>> -+-
>>303311 |
>>
>> -
>> usesysid |
>> -+-
>> 16387 |
>>
>> -
>> usename  |
>> -+
>> xyz  |
>>
>> -
>>
>>current_query   |
>> --+-
>>  |
>>
>> --
>>
>> waiting |
>> +
>> f   |
>>
>> -
>>xact_start   |
>> ---+
>> |
>>
>> ---
>>query_start  |
>> ---+
>>   2015-12-18 12:01:05.553534-06 |
>>
>> ---
>>   backend_start | client_addr | client_port
>> ---+-+-
>>   2015-12-18 12:01:05.547759-06 | |  -1
>>
>>
>> []s
>> Alex
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Melvin Davidson]

>
*ERROR:  syntax error at or near "1" at character 1*


*That indicates a problem restoring data, but since it's probably from a
previous restore attempt, and there is no time stamp, you might be able to
ignore it.*

*Do you have PgAdmin installed so you can look at the current contents of
the restored database?*

On Wed, Dec 23, 2015 at 3:06 PM, Killian Driscoll  wrote:

> On 23 December 2015 at 20:59, Melvin Davidson 
> wrote:
>
>> >OK - I see the logs there - the last log was almost 12 hrs ago, so no
>> recent one.
>>
>> That's not very helpful. Depending on how you've configured the logging,
>> PostgreSQL may only create one log file a day.
>>
> I didn't change a configuration, so it is how it came 'out of the box'
>
>>
>> Conventional thinking is It's what is IN the log file that is important.
>>
> Apologies - I have looked, but what's in it doesn't appear to relate to
> the current pg_restore as it was logged many hours before so I didn't
> mention it, and its a 13mb file so I didn't attach it.
>
> One thing I see it says
>
>
> *ERROR:  syntax error at or near "1" at character 1STATEMENT:  1
>  
> 010620E610010001030001004900A2EA743BD46B1DC0EC7756B43F1*
> with the latter part being similar to the alphanumeric I see currently
> running in the windows shell. This is all a bit above my pay grade as
> you've probably gathered
>
>
>>
>> On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <
>> killiandrisc...@gmail.com> wrote:
>>
>>> On 23 December 2015 at 20:38, Melvin Davidson 
>>> wrote:
>>>
 You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
 your_output.txt
 then use notepad to see the result.

 But from your last reply, it looks like Postgres is finishing the
 restore by VACUUMing the database. That means it has to vacuum every table.
 Since you never told us the info about whether you have a 32 or 64 bit
 system, how much memory, what processor speed, etc, it's hard to say how
 long it will take.
 But if you have no errors in the postgresql log (after the restore
 completed), you should be fine.
 It's usually located in\data\pg_log

>>>
>>> OK - I see the logs there - the last log was almost 12 hrs ago, so no
>>> recent one.
>>>
>>>

 On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <
 killiandrisc...@gmail.com> wrote:

> On 23 December 2015 at 20:24, Adrian Klaver  > wrote:
>
>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>
>>> On 23 December 2015 at 20:14, Adrian Klaver <
>>> adrian.kla...@aklaver.com
>>> > wrote:
>>>
>>
>>
>>
>>>
>>> So how are you determining it is running and that it is not doing
>>> anything?
>>>
>>> Since I ran the restore the windows shell has been 'active' with
>>> codes
>>> lines running - I took that as being active.!
>>>
>>
>> So what do they say?
>>
>> If I 'slow down' the code by using the scroll on the right I can see
> words from the db I recognise, but the rest is very long alphanumeric
> characters.
>
>>
>>>
>>> What does the Postgres log for the 9.4 instance show?
>>>
>>> Where is the log - here Control Panel\System and
>>> Security\Administrative
>>> Tools in event viewer or elsewhere?
>>>
>>
>> My guess in the Bitnami directory tree under logs/ or something
>> similar.
>>
>
> I can't see a log folder.
>
>>
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   --
>>>   Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>  >> 
>>>  >> >>
>>>
>>>
>>>
>>>
>>>  --
>>>  Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>


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

>>>
>>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  

Re: [GENERAL] Transfer db from one port to another

[John R Pierce]

I was half awake early this AM and perused my email, and noted a fubar 
in one of the command sets shown to you but too sleepy to try and reply 
at the time, and now this thread is WAY too silly long to find it again, 
but I think someone neglected to put the database name on a pg_restore 
command. if you don't give pg_restore a dbname, it just spews the 
SQL out on the console, which is what it sounded like Killian may have 
described.



yeah, this one from Adrian, at 7:02am PST (Z-0800) this morning

Per previous posts you want, whenever possible, to us a newer version 
of pg_dump to move a database from an older version(9.3) to a newer 
one(9.4). Therefore you should do your dump and restore using the 
pg_dump.exe and pg_restore.exe from the Bitanami bin directory. I 
would cd to the above directory and do:


pg_dump -V
pg_restore -V

to make sure the programs are found and are the 9.4 versions.

Then do:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out 


that last needs to have -d newdbname where newdbname has already 
been created, for instance, by...


C:\Bitnami\wappstack-5.5.30-0\postgresql\bin\createdb -p 5532 -U 
postgres newdbname


BEFORE you can restore to it


anyways, this thread has gone on WAY too long, Killian needs to learn 
the basics of command line operations on MS Windows, which admittedly 
are rather klunky, but are quite outside the charter of the 
pgsql-general email list.





p.s.   please don't CC me answers, I get every email sent to the list, 
with the CC's I end up getting two of every one.


--
john r pierce, recycling bits in santa cruz



--
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] ERROR: could not read block 3 in file "base/12511/12270"

[David G. Johnston]

On Wed, Dec 23, 2015 at 5:48 PM, Adrian Klaver 
wrote:

> On 12/23/2015 04:17 PM, Paul Jones wrote:
>
>> I have been having disk errors that have corrupted something in
>> my postgres database.  Other databases work ok:
>>
>> Running on Ubuntu 10.04.
>>
>> paul@kitanglad:~$ psql -U postgres
>> psql (9.4.5)
>> Type "help" for help.
>>
>> postgres=# SELECT pg_catalog.pg_is_in_recovery();
>> ERROR:  could not read block 3 in file "base/12511/12270": read only 4096
>> of 8192 bytes
>> postgres=# \c pjtest
>> You are now connected to database "pjtest" as user "postgres".
>> pjtest=# SELECT pg_catalog.pg_is_in_recovery();
>>   pg_is_in_recovery
>>   ---
>>f
>> (1 row)
>>
>>
>> Since this is the "postgres" database, dropping and re-creating it
>> doesn't seem possible.  pg_dump also gets the same error when I run
>> it on "postgres" so pg_basebackup will probably get the same error.
>>
>> The only thing I can think of is to create a completely new data directory
>> with initdb and pg_dump/restore all the databases in the cluter to the
>> new data directory.
>>
>> Is this my only option?
>>
>
> No, the 'postgres' database is one of the system databases created from a
> template
>
>
​This isn't the best characterization...​the "postgres" data is not a
"system" database but rather a convenient default user database.  Maybe I'm
being overly picky here but seeing "system" in this context does have a
connotation that we really don't want to impart onto the "postgres"
database.

It is named such because the default user is likewise "postgres" and most
utilities when not provided with a database name will use the O/S user's
name which, for administrative tasks, is likely to be "postgres" (you
really shouldn't use root for DB-admin stuff) and thus those commands will
be able to connect without much, if any, additional options supplied.

Its presence, absence, or modification in now way alters the fundamental
operation of PostgreSQL; though its lack may frustrate users acclimated to
using said defaults.

David J.

Re: [GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

[pbj]

On Wednesday, December 23, 2015 6:45 PM, Tom Lane  wrote:

 > Paul Jones  writes:
 > > I have been having disk errors that have corrupted something in
 > > my postgres database.  Other databases work ok:
 > 
 > > postgres=# SELECT pg_catalog.pg_is_in_recovery();
 > > ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 
 > > of 8192 bytes
 > 
 > Hm.  Evidently you've got a partially truncated file for some system
 > catalog or index.  It's fairly hard to estimate the consequences of
 > that without knowing which one it is.  Please see if this works:
 > 
 > $ export PGOPTIONS="-c ignore_system_indexes=true"
 > $ psql -U postgres
 > 
 > # show ignore_system_indexes;
 > (should say "on")
 > 
 > # select relname, relkind from pg_class where pg_relation_filenode(oid) = 
 > 12270;

paul@kitanglad:~$ export PGOPTIONS="-c ignore_system_indexes=true"
paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# show ignore_system_indexes;
 ignore_system_indexes 
---
 on
(1 row)

postgres=# select relname, relkind from pg_class where 
pg_relation_filenode(oid) = 12270;
  relname  | relkind 
---+-
 pg_proc_oid_index | i

(1 row)

postgres=# reindex index pg_proc_oid_index;
REINDEX
postgres=# \q
paul@kitanglad:~$ unset PGOPTIONS
paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# SELECT pg_catalog.pg_is_in_recovery();
 pg_is_in_recovery 
---
 f
(1 row)


So, it was an index and was quickly fixed.

Thanks!

>
 > 
 > If that works, and it tells you filenode 12270 is an index, you're in
 > luck: just REINDEX that index and you're done (at least with this problem,
 > there might be more lurking behind it).  Don't forget to unset PGOPTIONS
 > afterwards.
 > 
 > 
 > > Since this is the "postgres" database, dropping and re-creating it
 > > doesn't seem possible.
 > 
 > Sure it is, as long as you issue the commands from a non-broken database:
 > 
 > # drop database postgres;
 > DROP DATABASE
 > # create database postgres with template template0;
 > CREATE DATABASE
 > 
 > If you don't have any custom objects in the postgres database, this would
 > be by far the easiest way out.

Good to know!  I thought there was something special about "postgres".
I have not modified it from what initdb put there.

 > 
 > 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

Re: [GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

[pbj]

 > On 12/23/2015 04:17 PM, Paul Jones wrote:
 > >
 > >I have been having disk errors that have corrupted something in
 > >>my postgres database.  Other databases work ok:
 > 
 > This isn't the best characterization...the "postgres" data is not a "system" 
 > database but rather a convenient default user database.  Maybe I'm being 
 > overly picky here but seeing "system" in this context does have a 
 > connotation that we really don't want to impart onto the "postgres" database.
 > 
 > It is named such because the default user is likewise "postgres" and most 
 > utilities when not provided with a database name will use the O/S user's 
 > name which, for administrative tasks, is likely to be "postgres" (you really 
 > shouldn't use root for DB-admin stuff) and thus those commands will be able 
 > to connect without much, if any, additional options supplied.
 > 
 > Its presence, absence, or modification in now way alters the fundamental 
 > operation of PostgreSQL; though its lack may frustrate users acclimated to 
 > using said defaults.
 > 

This was one of the big lessons I learned from this.  All this time I was
under the mistaken impression that it was special.

 > David J.


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

[GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

[Paul Jones]

I have been having disk errors that have corrupted something in
my postgres database.  Other databases work ok:

Running on Ubuntu 10.04.

paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# SELECT pg_catalog.pg_is_in_recovery();
ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 
8192 bytes
postgres=# \c pjtest
You are now connected to database "pjtest" as user "postgres".
pjtest=# SELECT pg_catalog.pg_is_in_recovery();
 pg_is_in_recovery 
 ---
  f
(1 row)


Since this is the "postgres" database, dropping and re-creating it
doesn't seem possible.  pg_dump also gets the same error when I run
it on "postgres" so pg_basebackup will probably get the same error.

The only thing I can think of is to create a completely new data directory
with initdb and pg_dump/restore all the databases in the cluter to the
new data directory.

Is this my only option?

PJ


-- 
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] ERROR: could not read block 3 in file "base/12511/12270"

[Tom Lane]

Paul Jones  writes:
> I have been having disk errors that have corrupted something in
> my postgres database.  Other databases work ok:

> postgres=# SELECT pg_catalog.pg_is_in_recovery();
> ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 
> 8192 bytes

Hm.  Evidently you've got a partially truncated file for some system
catalog or index.  It's fairly hard to estimate the consequences of
that without knowing which one it is.  Please see if this works:

$ export PGOPTIONS="-c ignore_system_indexes=true"
$ psql -U postgres

# show ignore_system_indexes;
(should say "on")

# select relname, relkind from pg_class where pg_relation_filenode(oid) = 12270;

If that works, and it tells you filenode 12270 is an index, you're in
luck: just REINDEX that index and you're done (at least with this problem,
there might be more lurking behind it).  Don't forget to unset PGOPTIONS
afterwards.

> Since this is the "postgres" database, dropping and re-creating it
> doesn't seem possible.

Sure it is, as long as you issue the commands from a non-broken database:

# drop database postgres;
DROP DATABASE
# create database postgres with template template0;
CREATE DATABASE

If you don't have any custom objects in the postgres database, this would
be by far the easiest way out.

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

Re: [GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

[Adrian Klaver]

On 12/23/2015 04:17 PM, Paul Jones wrote:

I have been having disk errors that have corrupted something in
my postgres database.  Other databases work ok:

Running on Ubuntu 10.04.

paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# SELECT pg_catalog.pg_is_in_recovery();
ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 
8192 bytes
postgres=# \c pjtest
You are now connected to database "pjtest" as user "postgres".
pjtest=# SELECT pg_catalog.pg_is_in_recovery();
  pg_is_in_recovery
  ---
   f
(1 row)


Since this is the "postgres" database, dropping and re-creating it
doesn't seem possible.  pg_dump also gets the same error when I run
it on "postgres" so pg_basebackup will probably get the same error.

The only thing I can think of is to create a completely new data directory
with initdb and pg_dump/restore all the databases in the cluter to the
new data directory.

Is this my only option?


No, the 'postgres' database is one of the system databases created from 
a template


First make sure you have backups of the databases that are not corrupted.

Second have you done any modifications to the 'postgres' database?

If not, then from here:

http://www.postgresql.org/docs/9.4/static/manage-ag-templatedbs.html

"

Note: template1 and template0 do not have any special status beyond 
the fact that the name template1 is the default source database name for 
CREATE DATABASE. For example, one could drop template1 and recreate it 
from template0 without any ill effects. This course of action might be 
advisable if one has carelessly added a bunch of junk in template1. (To 
delete template1, it must have pg_database.datistemplate = false.)


The postgres database is also created when a database cluster is 
initialized. This database is meant as a default database for users and 
applications to connect to. It is simply a copy of template1 and can be 
dropped and recreated if necessary.


"

Have you corrected the disk error situation?
If not, then make plans to create a new cluster somewhere else..



PJ





--
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] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:59, Melvin Davidson  wrote:

> >OK - I see the logs there - the last log was almost 12 hrs ago, so no
> recent one.
>
> That's not very helpful. Depending on how you've configured the logging,
> PostgreSQL may only create one log file a day.
>
I didn't change a configuration, so it is how it came 'out of the box'

>
> Conventional thinking is It's what is IN the log file that is important.
>
Apologies - I have looked, but what's in it doesn't appear to relate to the
current pg_restore as it was logged many hours before so I didn't mention
it, and its a 13mb file so I didn't attach it.

One thing I see it says


*ERROR:  syntax error at or near "1" at character 1STATEMENT:  1
 
010620E610010001030001004900A2EA743BD46B1DC0EC7756B43F1*
with the latter part being similar to the alphanumeric I see currently
running in the windows shell. This is all a bit above my pay grade as
you've probably gathered


>
> On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:38, Melvin Davidson 
>> wrote:
>>
>>> You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
>>> your_output.txt
>>> then use notepad to see the result.
>>>
>>> But from your last reply, it looks like Postgres is finishing the
>>> restore by VACUUMing the database. That means it has to vacuum every table.
>>> Since you never told us the info about whether you have a 32 or 64 bit
>>> system, how much memory, what processor speed, etc, it's hard to say how
>>> long it will take.
>>> But if you have no errors in the postgresql log (after the restore
>>> completed), you should be fine.
>>> It's usually located in\data\pg_log
>>>
>>
>> OK - I see the logs there - the last log was almost 12 hrs ago, so no
>> recent one.
>>
>>
>>>
>>> On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <
>>> killiandrisc...@gmail.com> wrote:
>>>
 On 23 December 2015 at 20:24, Adrian Klaver 
 wrote:

> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:14, Adrian Klaver <
>> adrian.kla...@aklaver.com
>> > wrote:
>>
>
>
>
>>
>> So how are you determining it is running and that it is not doing
>> anything?
>>
>> Since I ran the restore the windows shell has been 'active' with codes
>> lines running - I took that as being active.!
>>
>
> So what do they say?
>
> If I 'slow down' the code by using the scroll on the right I can see
 words from the db I recognise, but the rest is very long alphanumeric
 characters.

>
>>
>> What does the Postgres log for the 9.4 instance show?
>>
>> Where is the log - here Control Panel\System and
>> Security\Administrative
>> Tools in event viewer or elsewhere?
>>
>
> My guess in the Bitnami directory tree under logs/ or something
> similar.
>

 I can't see a log folder.

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


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

Re: [GENERAL] Transfer db from one port to another

[Adrian Klaver]

On 12/23/2015 12:12 PM, John R Pierce wrote:

I was half awake early this AM and perused my email, and noted a fubar
in one of the command sets shown to you but too sleepy to try and reply
at the time, and now this thread is WAY too silly long to find it again,
but I think someone neglected to put the database name on a pg_restore
command. if you don't give pg_restore a dbname, it just spews the
SQL out on the console, which is what it sounded like Killian may have
described.


yeah, this one from Adrian, at 7:02am PST (Z-0800) this morning


Per previous posts you want, whenever possible, to us a newer version
of pg_dump to move a database from an older version(9.3) to a newer
one(9.4). Therefore you should do your dump and restore using the
pg_dump.exe and pg_restore.exe from the Bitanami bin directory. I
would cd to the above directory and do:

pg_dump -V
pg_restore -V

to make sure the programs are found and are the 9.4 versions.

Then do:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out


that last needs to have -d newdbname where newdbname has already
been created, for instance, by...


Aah, my mistake. Yes you need to specify the database to get the restore 
to work properly. Also explains why there is nothing in the logs.




 C:\Bitnami\wappstack-5.5.30-0\postgresql\bin\createdb -p 5532 -U
postgres newdbname

BEFORE you can restore to it


anyways, this thread has gone on WAY too long, Killian needs to learn
the basics of command line operations on MS Windows, which admittedly
are rather klunky, but are quite outside the charter of the
pgsql-general email list.




p.s.   please don't CC me answers, I get every email sent to the list,
with the CC's I end up getting two of every one.\


FYI, you can go here:

http://www.postgresql.org/mailpref/pgsql-general

and set eliminatecc to have the listserver do that for you.






--
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] Transfer db from one port to another

[Melvin Davidson]

You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
your_output.txt
then use notepad to see the result.

But from your last reply, it looks like Postgres is finishing the restore
by VACUUMing the database. That means it has to vacuum every table.
Since you never told us the info about whether you have a 32 or 64 bit
system, how much memory, what processor speed, etc, it's hard to say how
long it will take.
But if you have no errors in the postgresql log (after the restore
completed), you should be fine.
It's usually located in\data\pg_log

On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll  wrote:

> On 23 December 2015 at 20:24, Adrian Klaver 
> wrote:
>
>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>
>>> On 23 December 2015 at 20:14, Adrian Klaver >> > wrote:
>>>
>>
>>
>>
>>>
>>> So how are you determining it is running and that it is not doing
>>> anything?
>>>
>>> Since I ran the restore the windows shell has been 'active' with codes
>>> lines running - I took that as being active.!
>>>
>>
>> So what do they say?
>>
>> If I 'slow down' the code by using the scroll on the right I can see
> words from the db I recognise, but the rest is very long alphanumeric
> characters.
>
>>
>>>
>>> What does the Postgres log for the 9.4 instance show?
>>>
>>> Where is the log - here Control Panel\System and Security\Administrative
>>> Tools in event viewer or elsewhere?
>>>
>>
>> My guess in the Bitnami directory tree under logs/ or something similar.
>>
>
> I can't see a log folder.
>
>>
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   --
>>>   Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>  >> 
>>>  >> >>
>>>
>>>
>>>
>>>
>>>  --
>>>  Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>


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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:38, Melvin Davidson  wrote:

> You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
> your_output.txt
> then use notepad to see the result.
>
> But from your last reply, it looks like Postgres is finishing the restore
> by VACUUMing the database. That means it has to vacuum every table.
> Since you never told us the info about whether you have a 32 or 64 bit
> system, how much memory, what processor speed, etc, it's hard to say how
> long it will take.
> But if you have no errors in the postgresql log (after the restore
> completed), you should be fine.
> It's usually located in\data\pg_log
>

OK - I see the logs there - the last log was almost 12 hrs ago, so no
recent one.


>
> On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:24, Adrian Klaver 
>> wrote:
>>
>>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>>
 On 23 December 2015 at 20:14, Adrian Klaver > wrote:

>>>
>>>
>>>

 So how are you determining it is running and that it is not doing
 anything?

 Since I ran the restore the windows shell has been 'active' with codes
 lines running - I took that as being active.!

>>>
>>> So what do they say?
>>>
>>> If I 'slow down' the code by using the scroll on the right I can see
>> words from the db I recognise, but the rest is very long alphanumeric
>> characters.
>>
>>>

 What does the Postgres log for the 9.4 instance show?

 Where is the log - here Control Panel\System and Security\Administrative
 Tools in event viewer or elsewhere?

>>>
>>> My guess in the Bitnami directory tree under logs/ or something similar.
>>>
>>
>> I can't see a log folder.
>>
>>>
>>>











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




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




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



>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Transfer db from one port to another

[Melvin Davidson]

>OK - I see the logs there - the last log was almost 12 hrs ago, so no
recent one.

That's not very helpful. Depending on how you've configured the logging,
PostgreSQL may only create one log file a day.

Conventional thinking is It's what is IN the log file that is important.

On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll  wrote:

> On 23 December 2015 at 20:38, Melvin Davidson 
> wrote:
>
>> You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
>> your_output.txt
>> then use notepad to see the result.
>>
>> But from your last reply, it looks like Postgres is finishing the restore
>> by VACUUMing the database. That means it has to vacuum every table.
>> Since you never told us the info about whether you have a 32 or 64 bit
>> system, how much memory, what processor speed, etc, it's hard to say how
>> long it will take.
>> But if you have no errors in the postgresql log (after the restore
>> completed), you should be fine.
>> It's usually located in\data\pg_log
>>
>
> OK - I see the logs there - the last log was almost 12 hrs ago, so no
> recent one.
>
>
>>
>> On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <
>> killiandrisc...@gmail.com> wrote:
>>
>>> On 23 December 2015 at 20:24, Adrian Klaver 
>>> wrote:
>>>
 On 12/23/2015 11:17 AM, Killian Driscoll wrote:

> On 23 December 2015 at 20:14, Adrian Klaver  > wrote:
>



>
> So how are you determining it is running and that it is not doing
> anything?
>
> Since I ran the restore the windows shell has been 'active' with codes
> lines running - I took that as being active.!
>

 So what do they say?

 If I 'slow down' the code by using the scroll on the right I can see
>>> words from the db I recognise, but the rest is very long alphanumeric
>>> characters.
>>>

>
> What does the Postgres log for the 9.4 instance show?
>
> Where is the log - here Control Panel\System and
> Security\Administrative
> Tools in event viewer or elsewhere?
>

 My guess in the Bitnami directory tree under logs/ or something similar.

>>>
>>> I can't see a log folder.
>>>


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

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

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


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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 21:06, Killian Driscoll 
wrote:

> On 23 December 2015 at 20:59, Melvin Davidson 
> wrote:
>
>> >OK - I see the logs there - the last log was almost 12 hrs ago, so no
>> recent one.
>>
>> That's not very helpful. Depending on how you've configured the logging,
>> PostgreSQL may only create one log file a day.
>>
> I didn't change a configuration, so it is how it came 'out of the box'
>
>>
>> Conventional thinking is It's what is IN the log file that is important.
>>
> Apologies - I have looked, but what's in it doesn't appear to relate to
> the current pg_restore as it was logged many hours before so I didn't
> mention it, and its a 13mb file so I didn't attach it.
>
> One thing I see it says
>
>
> *ERROR:  syntax error at or near "1" at character 1STATEMENT:  1
>  
> 010620E610010001030001004900A2EA743BD46B1DC0EC7756B43F1*
> with the latter part being similar to the alphanumeric I see currently
> running in the windows shell. This is all a bit above my pay grade as
> you've probably gathered
>

I forgot to say, as this log is from earlier today, I guess it is from my
earlier attempts to do this before I sought help from this group?

>
>
>>
>> On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <
>> killiandrisc...@gmail.com> wrote:
>>
>>> On 23 December 2015 at 20:38, Melvin Davidson 
>>> wrote:
>>>
 You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 >
 your_output.txt
 then use notepad to see the result.

 But from your last reply, it looks like Postgres is finishing the
 restore by VACUUMing the database. That means it has to vacuum every table.
 Since you never told us the info about whether you have a 32 or 64 bit
 system, how much memory, what processor speed, etc, it's hard to say how
 long it will take.
 But if you have no errors in the postgresql log (after the restore
 completed), you should be fine.
 It's usually located in\data\pg_log

>>>
>>> OK - I see the logs there - the last log was almost 12 hrs ago, so no
>>> recent one.
>>>
>>>

 On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <
 killiandrisc...@gmail.com> wrote:

> On 23 December 2015 at 20:24, Adrian Klaver  > wrote:
>
>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>
>>> On 23 December 2015 at 20:14, Adrian Klaver <
>>> adrian.kla...@aklaver.com
>>> > wrote:
>>>
>>
>>
>>
>>>
>>> So how are you determining it is running and that it is not doing
>>> anything?
>>>
>>> Since I ran the restore the windows shell has been 'active' with
>>> codes
>>> lines running - I took that as being active.!
>>>
>>
>> So what do they say?
>>
>> If I 'slow down' the code by using the scroll on the right I can see
> words from the db I recognise, but the rest is very long alphanumeric
> characters.
>
>>
>>>
>>> What does the Postgres log for the 9.4 instance show?
>>>
>>> Where is the log - here Control Panel\System and
>>> Security\Administrative
>>> Tools in event viewer or elsewhere?
>>>
>>
>> My guess in the Bitnami directory tree under logs/ or something
>> similar.
>>
>
> I can't see a log folder.
>
>>
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   --
>>>   Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>  >> 
>>>  >> >>
>>>
>>>
>>>
>>>
>>>  --
>>>  Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>> >> >
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>


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

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

Re: [GENERAL] Transfer db from one port to another

[John R Pierce]

On 12/23/2015 12:37 PM, Adrian Klaver wrote:

p.s.   please don't CC me answers, I get every email sent to the list,
with the CC's I end up getting two of every one.\


FYI, you can go here:

http://www.postgresql.org/mailpref/pgsql-general

and set eliminatecc to have the listserver do that for you.



except, its the one from the list that I want to keep in my archive.   
my filter isn't sophisticated enough to separate them (I'm just 
filtering on Subject contains: [GENERAL])






--
john r pierce, recycling bits in santa cruz



--
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] Transfer db from one port to another

[Tom Lane]

John R Pierce  writes:
>> FYI, you can go here:
>> http://www.postgresql.org/mailpref/pgsql-general
>> and set eliminatecc to have the listserver do that for you.

> except, its the one from the list that I want to keep in my archive.   
> my filter isn't sophisticated enough to separate them (I'm just 
> filtering on Subject contains: [GENERAL])

Maybe drop messages that contain that *and* are not from the list bounce
address, ie envelope sender isn't pgsql-general-owner...@postgresql.org ?

Actually, rather than looking at the Subject: at all, you should look
to see if pgsql-general@postgresql.org is in To: or cc:, and combine
that with the wrong-sender filter.  That would avoid dropping private
replies.

Reply-to-all is a sufficiently ingrained habit around here that there
is exactly no chance people will do your filtering for you.  The reasons
for that are partly historical no doubt (the listserv used to be a lot
less reliable/prompt than it is now), but the custom is established.

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

Re: [GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

[Tom Lane]

 writes:
>>> On 12/23/2015 04:17 PM, Paul Jones wrote:
>>> This isn't the best characterization...the "postgres" data is not a 
>>> "system" database but rather a convenient default user database.  Maybe I'm 
>>> being overly picky here but seeing "system" in this context does have a 
>>> connotation that we really don't want to impart onto the "postgres" 
>>> database.

> This was one of the big lessons I learned from this.  All this time I was
> under the mistaken impression that it was special.

FWIW, there really aren't any special databases in a Postgres
installation.  As Paul already explained, "postgres" exists mainly to
provide a convenient default landing point for utility applications.
The only thing magic about it is the name --- you can drop it, and
create a new database with that same name, and be none the worse for
wear.  Or do without, if you don't mind specifying a different landing
point.

The other two created-by-default databases, template0 and template1,
are similarly not really hardwired in.  template1 is hardwired to
the extent that CREATE DATABASE operates by copying whatever database
is named "template1" (in the absence of a different TEMPLATE option).
That's about it.  There is a convention that template0 should be an
absolutely unmodified virgin copy of the state created by initdb,
but it's only a convention not something that the core database code
either enforces or depends on.

Now, if you were to try to drop either of those databases, you'd get
a Bronx cheer:

regression=# drop database template1;
ERROR:  cannot drop a template database
regression=# drop database template0;
ERROR:  cannot drop a template database

but that's only driven by the fact that pg_database.datistemplate is set
for them (and that flag does little more than allow nonprivileged users
to select them as sources for CREATE DATABASE).  If you were up against
the wall in trying to reconstitute a broken installation, you could
manually unset the datistemplate flag for either, drop it, and clone it
from the other one.

In short, there's less magic here than meets the eye.

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

Re: [GENERAL] efficient math vector operations on arrays

[Pavel Stehule]

2015-12-24 8:34 GMT+01:00 Marcus Engene :

> On 24/12/15 07:13, Pavel Stehule wrote:
>
> Hi
>
> 2015-12-24 8:05 GMT+01:00 Marcus Engene :
>
>> Hi,
>>
>> Are there highly efficient C extensions out there for math operations on
>> arrays? Dot product and whatnot.
>>
>
> what you mean "highly efficient" ?
>
>
> Implemented as a C module so I wont have to unnest or plpgsql.
>

ok,

I don't know any extension that calculate euclid distance, but it should be
trivial in C - if you don't need to use generic types and generic
operations.

Pavel


> Kind regards,
> Marcus
>
>

[GENERAL] efficient math vector operations on arrays

[Marcus Engene]

Hi,

Are there highly efficient C extensions out there for math operations on 
arrays? Dot product and whatnot.


Example usecase: sort an item by euclid distance.

Kind regards,
Marcus



--
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] efficient math vector operations on arrays

[Pavel Stehule]

Hi

2015-12-24 8:05 GMT+01:00 Marcus Engene :

> Hi,
>
> Are there highly efficient C extensions out there for math operations on
> arrays? Dot product and whatnot.
>

what you mean "highly efficient" ?

PostgreSQL executor is interpret - so in almost all cases the special
optimizations has not big sense. If you take few us, you will lost in
executor.

>
> Example usecase: sort an item by euclid distance.
>

some is in intarray http://www.postgresql.org/docs/9.4/static/intarray.html

Regards

Pavel



>
> Kind regards,
> Marcus
>
>
>
> --
> 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] efficient math vector operations on arrays

[Marcus Engene]

On 24/12/15 07:13, Pavel Stehule wrote:

Hi

2015-12-24 8:05 GMT+01:00 Marcus Engene >:


Hi,

Are there highly efficient C extensions out there for math
operations on arrays? Dot product and whatnot.


what you mean "highly efficient" ?


Implemented as a C module so I wont have to unnest or plpgsql.

Kind regards,
Marcus

Re: [GENERAL] Transfer db from one port to another

[John R Pierce]

On 12/23/2015 2:25 AM, Killian Driscoll wrote:
Sorry, forgot to add: once I get the warning that the Pg_dump can't be 
found there is then a password prompt; I tried the db password and the 
pc password but both fail:


Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed: 
FATAL:  p

assword authentication failed for user "killian"


note that databases don't have passwords, database USERS have 
passwords.  'killian' probably doesn't have a database user, and since 
you didn't specify a user, it defaulted to your system username 
(expecting that user to have been created in postgres, and wanting that 
probably non-existant postgres users passsword)


so, ok, try the command with -U postgres, as *
*

   *pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore -U
   postgres -p 5532*

if/when it prompts for a password, thats the password of the 'postgres' 
database user, as configured in the postgres servers.


note it will prompt for the password a couple times,  once for postgres 
on port 5432, and again for postgres on port 5532, at least if both 
database services are configured to require passwords for local connections.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:26
To: John R Pierce 
Cc: pgsql-general 
Subject: Re: [GENERAL] Transfer db from one port to another

 

 

On 23 December 2015 at 11:19, Killian Driscoll  > wrote:

On 23 December 2015 at 11:07, John R Pierce  > wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc  > dump.sql

pg_restore -h localhost -p 5532 dump.sql

 

I tried this, but nothing appears to happen when entering the commands. 
Attached is a screenshot of the shell window - what am I doing wrong? 



those are system shell commands, not psql sql commands.  catch-22, in the 
windows environment, postgresql's command tools probably aren't in the path, so 
to execute the above commands try this...

start -> run ->  CMD 

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> path "c:\Program Files\PostgreSQL\9.4\bin";%path%
C:\Users\YourName> pg_dump -Fc -p 5432  | pg_restore -p 5532

Thanks. When I do this I get an error: could not find a "pg_dump" to execute - 
I've used the path "C:\Program Files\PostgreSQL\9.3\bin";%path% which appears 
to be correct 

Sorry, forgot to add: once I get the warning that the Pg_dump can't be found 
there is then a password prompt; I tried the db password and the pc password 
but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed: FATAL:  p
assword authentication failed for user "killian"

 

  Do you have a user killian in the database? If not you can either create 
it or use pg_dump with the –U switch to set it to the existing user (and with 
privileges on the database of course).


pg_restore: [archiver] input file is too short (read 0, expected 5)
 



if your postgres is installed somewhere else, replace "c:\Program 
Files\PostgreSQL\9.4\bin" in the PATH command with its actual location \bin  









-- 
john r pierce, recycling bits in santa cruz

 

 

Re: [GENERAL] Transfer db from one port to another

[John R Pierce]

On 12/23/2015 2:19 AM, Killian Driscoll wrote:


(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName>***path "c:\Program
Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>*pg_dump -Fc -p 5432 *//*|
pg_restore -p 5532*

Thanks. When I do this I get an error: could not find a "pg_dump" to 
execute - I've used the path *"C:\Program 
Files\PostgreSQL\9.3\bin";%path% *which appears to be correct


to test this, try (at that same command prompt)

   *dir **"C:\Program Files\PostgreSQL\9.3\bin"
   *

that should list the postgres utilities and stuff, including psql.exe, 
pg_dump.exe, etc.


like, on a system here (which uses a non-default path), its ...

   C:\Users\>dir "d:\PostgreSQL\9.3\bin"
 Volume in drive D is Drive_Dee
 Volume Serial Number is 

 Directory of d:\PostgreSQL\9.3\bin

   09/30/2014  12:28 AM  .
   09/30/2014  12:28 AM  ..
   07/21/2014  11:51 PM69,120 clusterdb.exe
   ...
   07/21/2014  11:51 PM   384,000 pg_dump.exe
   ...
   07/21/2014  11:51 PM   180,736 pg_restore.exe
   ...
   07/21/2014  11:51 PM   408,576 psql.exe
   ...
   02/05/2014  01:33 AM77,824 zlib1.dll
  70 File(s) 56,177,637 bytes
   3 Dir(s)  1,687,179,091,968 bytes free

   C:\Users\>


(note I trimmed about 65 files)



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Secret Santa List

[Alberto Cabello Sánchez]

On Wed, Dec 23, 2015 at 04:32:34AM -0500, Lou Duchez wrote:
> >Of course: you can't UPDATE a field with a query returning more than one
> >result, as you can check easily trying:
> 
> I understand that, and my query does not return more than one result.  The
> problem is that it returns THE SAME result each time, most likely because
> the subquery is evaluated exactly once and then the main query uses that
> single result over and over.
> 
> update secretsanta set recipient =
> ( select giver from secretsanta s2 where not exists (select * from
> secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
> 
> My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each
> time, and see that the set of available recipients has changed.

I see. As in most programming languages, the () clause is evaluated once 
and the result used instead.
 
> >You could get a list of givers in no particular order (e. g. "select giver
> >from secretsanta order by md5(concat(giver,current_time))") then setting
> >each employee as next's employee giver.
> 
> As in, write a loop in some programming language to update the table one row
> at a time, or did you envision a way to do this with an SQL statement?  I can 
> certainly write a loop, if that's the only solution.

I'm not aware of a SQL statement to do that. Maybe you will be able do it with
a CTE, as you can make a table with a field (1, 2, 3, 4) and take advantage of
the integer arithmetic (one problem with your original question is I don't know
how to ask for the next employee in plain SQL when the ID is a given name).

-- 
Alberto Cabello Sánchez
Universidad de Extremadura


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

[GENERAL] Transfer db from one port to another

[Killian Driscoll]

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and
postgreSQL 9.4 on port 5532 with the latter set up to use with Bitnami
stack to test php files I am generating from my db.

I want to transfer my db with three schemas from port 5432 to port 5532 to
use within the bitnami stack. I have used pgAdmin to create a backup.sql
and when using pgAdmin to restore the .sql to port 5532 I get the following
error

ERROR:  syntax error at or near "\"
LINE 26: \connect irll_project

with the lines in the .sql file created being

ALTER DATABASE irll_project OWNER TO postgres;

\connect irll_project

I have tried to use the Windows psql shell command to import using

\i 'C:/all.sql'

on pressing enter I see multiple occurrences saying invalid command, and
then it ends with

error: out of memory detail: failed on request of size 268435456

What is the correct command line?

Killian DriscoIl

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

Hello

 

Why don’t you simply change the port in postgresql.conf and restart the server?

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 09:58
To: pgsql-general 
Subject: [GENERAL] Transfer db from one port to another

 

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL 
9.4 on port 5532 with the latter set up to use with Bitnami stack to test php 
files I am generating from my db. 

I want to transfer my db with three schemas from port 5432 to port 5532 to use 
within the bitnami stack. I have used pgAdmin to create a backup.sql and when 
using pgAdmin to restore the .sql to port 5532 I get the following error

ERROR:  syntax error at or near "\"
LINE 26: \connect irll_project

with the lines in the .sql file created being

ALTER DATABASE irll_project OWNER TO postgres;

\connect irll_project


I have tried to use the Windows psql shell command to import using

\i 'C:/all.sql'

on pressing enter I see multiple occurrences saying invalid command, and then 
it ends with  

 

error: out of memory detail: failed on request of size 268435456

What is the correct command line?

Killian DriscoIl

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 10:05, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

> Hello
>
>
>
> Why don’t you simply change the port in postgresql.conf and restart the
> server?
>
I am attempting to learn to use the dump and restore, so would like to
figure out how to do it via an export and import.

>
>
> Bye
>
> Charles
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Killian Driscoll
> *Sent:* Mittwoch, 23. Dezember 2015 09:58
> *To:* pgsql-general 
> *Subject:* [GENERAL] Transfer db from one port to another
>
>
>
> I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and
> postgreSQL 9.4 on port 5532 with the latter set up to use with Bitnami
> stack to test php files I am generating from my db.
>
> I want to transfer my db with three schemas from port 5432 to port 5532 to
> use within the bitnami stack. I have used pgAdmin to create a backup.sql
> and when using pgAdmin to restore the .sql to port 5532 I get the following
> error
>
> ERROR:  syntax error at or near "\"
> LINE 26: \connect irll_project
>
> with the lines in the .sql file created being
>
> ALTER DATABASE irll_project OWNER TO postgres;
>
> \connect irll_project
>
>
> I have tried to use the Windows psql shell command to import using
>
> \i 'C:/all.sql'
>
> on pressing enter I see multiple occurrences saying invalid command, and
> then it ends with
>
>
>
> error: out of memory detail: failed on request of size 268435456
>
> What is the correct command line?
>
> Killian DriscoIl
>

Re: [GENERAL] Transfer db from one port to another

[Andreas Kretschmer]

Killian Driscoll  wrote:

> I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL
> 9.4 on port 5532 with the latter set up to use with Bitnami stack to test php
> files I am generating from my db.
> 
> I want to transfer my db with three schemas from port 5432 to port 5532 to use
> within the bitnami stack. I have used pgAdmin to create a backup.sql and when
> using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc  > dump.sql

pg_restore -h localhost -p 5532 dump.sql

(untestet, please read *before* the documentation)


I think, this should work. No idea what's wrong with pgAdmin, not using
that.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] Transfer db from one port to another

[Andreas Kretschmer]

Charles Clavadetscher  wrote:

> Hello
> 
>  
> 
> Why don’t you simply change the port in postgresql.conf and restart the 
> server?

i think, he wants the data from the one database within the other,
because of that he can't change the port.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] Secret Santa List

[Lou Duchez]

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:


I understand that, and my query does not return more than one result.  The 
problem is that it returns THE SAME result each time, most likely because the 
subquery is evaluated exactly once and then the main query uses that single 
result over and over.

update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );

My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each time, and see that the set of available recipients has 
changed.  If "Steve" was picked for the first row, "Steve" shouldn't be available for any subsequent row.  If 
"Fred" was picked for the second row, neither "Steve" nor "Fred" should be available for any 
subsequent row.


You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.


As in, write a loop in some programming language to update the table one row at 
a time, or did you envision a way to do this with an SQL statement?  I can 
certainly write a loop, if that's the only solution.

Thanks!



--
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] Shared system resources

[George Neuner]

On Tue, 22 Dec 2015 23:21:27 +, David Wilson 
wrote:

>On Linux the memory pages of an exiting process aren't sanitized at
>exit, however it is impossible(?) for userspace to reallocate them
>without the kernel first zeroing their contents.

Not impossible, but it requires a non-standard kernel.

Since 2.6.33, mmap() accepts the flag MAP_UNINITIALIZED which allows
pages to be mapped without being cleared.  The flag has no effect
unless the kernel was built with CONFIG_MMAP_ALLOW_UNINITIALIZED.


No mainstream distro enables this.  AFAIK, there is NO distro at all
that enables it ... it's too big a security risk for a general purpose
system.  It's intended to support embedded systems where the set of
programs is known.

George



-- 
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] Secret Santa List

[Alberto Cabello Sánchez]

On 23 December 2015 at 16:49, Lou Duchez  wrote:

> I have a company with four employees who participate in a Secret Santa
> program, where each buys a gift for an employee chosen at random.  (For
> now, I do not mind if an employee ends up buying a gift for himself.)  How
> can I make this work with an SQL statement?
>
> Here is the SQL statement I am using to populate the "recipient" column:
>
> --
> update secretsanta set recipient =
> ( select giver from secretsanta s2 where not exists (select * from
> secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
> --
>
> The problem: every time I run this, a single name is chosen at random and
> used to populate all the rows.  So all four rows will get a recipient of
> "Steve" or "Earl" or whatever single name is chosen at random.

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:

update secretsanta set recipient=(select giver from secretsanta);


You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.

-- 
Alberto Cabello Sánchez
Universidad de Extremadura


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