Re: [GENERAL] Regarding "Point-in-time Recovery" feature

[Ryan King]

I have archiving enabled for PITR but it has been causing problems. So I 
changed the archive_command to ‘cd .’ to disable for now.
However, I’m currently looking for a script/process that will rotate the logs 
every week or so, and also ensure archived wal files don’t pile up. 
I
> On Feb 27, 2015, at 2:55 AM, Jim Nasby  wrote:
> 
> On 2/23/15 5:39 AM, Saurabh Gupta A wrote:
>> Hello,
>> 
>> Has anybody used online backup feature of postgreSQL? In fact precise
>> postgreSQL term is called: "Point-in-time Recovery" (PITR)
>> This means enabling following additional options in config:
>> ---
>> archive_command = on
>> archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f' # This is
>> only example path
>> ---
>> 
>> If yes then may I know how it is used and how it impacts database
>> performance?
> 
> Other than the overhead of the copy itself, it generally doesn't. There are a 
> very limited number of shortcuts we can take when wal_level is set to 
> minimal, such as not WAL logging the full contents of data inserted into a 
> table that was created/truncated in the same transaction, but generally 
> archiving doesn't impact performance (assuming you keep it running correctly 
> so pg_xlog doesn't fill up ;)
> -- 
> Jim Nasby, Data Architect, Blue Treble Consulting
> 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



-- 
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] Copy Data between different databases

[Ryan King]

Have you considered using dblink() or foreign data wrappers to transfer the 
data? 
You can do a select from source, insert into target using one of these methods.

RC

> On Mar 3, 2015, at 12:09 PM, Francisco Olarte  wrote:
> 
> Hi Adrian:
> 
> On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver  > wrote:
> On 03/03/2015 06:18 AM, Tim Semmelhaack wrote:
> Hi,
> 
> I want to copy data between two servers (Version 9.1 and 9.4)
> 
> I've tried
> 
> ​​psql -h host1 -U user1 -d db1  -f /q1.sql | psql -h host2 -U user2 -d db2 -f
> /q2.sql
> 
> Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT
> ...) TO STDIN
> As a result nothing is copied.
> 
> When I run a much simpler version of the query with the -c "Select .."
> option it works. Because the sql-scripts are quite long, I don't to do it
> without the -f option.
> 
> Have you tried?:
> 
> psql -h host1 -U user1 -d db1 < /q1.sql | psql -h host2 -U user2 -d db2 < 
> /q2.sql
> 
> ​As you pointed, my bet is in the -f case COPY FROM STDIN expects the data on 
> the file ( otherwise pg_dumps would not work ), but your sugestion seems to 
> have a problem of double redirection, let me elaborate:
> 
> folarte@paqueton:~$ echo  > A
> folarte@paqueton:~$ echo  | cat < A
> 
> 
> ( in this case the A file will simulate q2.sql, echo  is simulating the 
> first psql command and cat is simulating the second psql command ). You are 
> redirecting the second psql input twice, one with | other with <. A simple 
> variant is:
> 
> folarte@paqueton:~$ (cat A; echo ) | cat
> 
> 
> 
> Which, translating back to psql, should be:
> 
> (cat q2.sql; ​psql -h host1 -U user1 -d db1  -f /q1.sql) | psql -h host2 -U 
> user2 -d db2 
> 
> Regards.
> Francisco Olarte.
> 
> 
> 
> 
> 
> 
> 
>  
> 
> 
> So where is the difference between the -c and the -f option?
> 
> Tim
> -- Semmelhaack(at)gmx(dot).de
> 
> 
> 
> 
> 
> 
> -- 
> 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] could not create shared memory segment: Invalid argument

[Ryan King - NOAA Affiliate]

Apologies ahead of time for not knowing which group to send to, but I
wanted to see if anyone has encountered and resolved this type of error.
I'm setting up postgresql 9.2 streaming replication on RH and after copying
the master data directory over to the slave, the psql service refuses start
and gives the following errors.



   2015-07-13 23:55:41.224 UTC FATAL:  could not create shared memory
segment: Invalid argument
   2015-07-13 23:55:41.224 UTC DETAIL:  Failed system call was
shmget(key=5432001, size=1146945536, 03600).
   2015-07-13 23:55:41.224 UTC HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAX parameter.  You can either reduce the request size or reconfigure
the kernel with larger SHMMAX.  To reduce the request size (currently
1146945536 bytes), reduce PostgreSQL's shared memory usage, perhaps by
reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less
than your kernel's SHMMIN parameter, in which case raising the request size
or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared
memory configuration.
   2015-07-13 23:56:21.344 UTC FATAL:  could not create shared memory
segment: Invalid argument
   2015-07-13 23:56:21.344 UTC DETAIL:  Failed system call was
shmget(key=5432001, size=58302464, 03600).
   2015-07-13 23:56:21.344 UTC HINT:  This error usually means that
PostgreSQL's request for a shared memory segment exceeded your kernel's
SHMMAX parameter.  You can either reduce the request size or reconfigure
the kernel with larger SHMMAX.  To reduce the request size (currently
58302464 bytes), reduce PostgreSQL's shared memory usage, perhaps by
reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less
than your kernel's SHMMIN parameter, in which case raising the request size
or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared
memory configuration.



I've set shared_buffer way down to next to nothing along with kernel.shmmax
and kernel.shmall per some blogs. However, the same error persists, and I'm
getting no where. I think ultimately the solution is to upgrade, but the
devs may not be ready for an upgrade at this point. Any help would be
greatly appreciated. Thanks!

Re: [GENERAL] could not create shared memory segment: Invalid argument

[Ryan King - NOAA Affiliate]

I tried that too - same result. I updated another box w/ the same issue to
9.4.4, and all is well there. Thanks for your reply.

On Tue, Jul 14, 2015 at 8:59 AM, Andy Colson  wrote:

> On 7/13/2015 7:08 PM, Ryan King - NOAA Affiliate wrote:
>
>> Apologies ahead of time for not knowing which group to send to, but I
>> wanted to see if anyone has encountered and resolved this type of error.
>> I'm setting up postgresql 9.2 streaming replication on RH and after
>> copying the master data directory over to the slave, the psql service
>> refuses start and gives the following errors.
>>
>>
>>
>> 2015-07-13 23:55:41.224 UTC FATAL:  could not create shared memory
>> segment: Invalid argument
>> 2015-07-13 23:55:41.224 UTC DETAIL:  Failed system call was
>> shmget(key=5432001, size=1146945536, 03600).
>> 2015-07-13 23:55:41.224 UTC HINT:  This error usually means that
>> PostgreSQL's request for a shared memory segment exceeded your kernel's
>> SHMMAX parameter.  You can either reduce the request size or reconfigure
>> the kernel with larger SHMMAX.  To reduce the request size (currently
>> 1146945536 bytes), reduce PostgreSQL's shared memory usage, perhaps by
>> reducing shared_buffers or max_connections.
>>  If the request size is already small, it's possible that it is
>> less than your kernel's SHMMIN parameter, in which case raising the
>> request size or reconfiguring SHMMIN is called for.
>>  The PostgreSQL documentation contains more information about
>> shared memory configuration.
>> 2015-07-13 23:56:21.344 UTC FATAL:  could not create shared memory
>> segment: Invalid argument
>> 2015-07-13 23:56:21.344 UTC DETAIL:  Failed system call was
>> shmget(key=5432001, size=58302464, 03600).
>> 2015-07-13 23:56:21.344 UTC HINT:  This error usually means that
>> PostgreSQL's request for a shared memory segment exceeded your kernel's
>> SHMMAX parameter.  You can either reduce the request size or reconfigure
>> the kernel with larger SHMMAX.  To reduce the request size (currently
>> 58302464 bytes), reduce PostgreSQL's shared memory usage, perhaps by
>> reducing shared_buffers or max_connections.
>>  If the request size is already small, it's possible that it is
>> less than your kernel's SHMMIN parameter, in which case raising the
>> request size or reconfiguring SHMMIN is called for.
>>  The PostgreSQL documentation contains more information about
>> shared memory configuration.
>>
>>
>>
>> I've set shared_buffer way down to next to nothing along with
>> kernel.shmmax and kernel.shmall per some blogs. However, the same error
>> persists, and I'm getting no where. I think ultimately the solution is
>> to upgrade, but the devs may not be ready for an upgrade at this point.
>> Any help would be greatly appreciated. Thanks!
>>
>
> You don't want to decrease kernel.shmmax you want to set it to the request
> size:
>
> sysctl -w kernel.shmmax=1146945536
>
> shmmax is the only thing you really need to play with.
>
> -Andy
>
>

[GENERAL] $libdir/mysql_fdw

[Ryan King - NOAA Affiliate]

When attempting to create the required functions and extensions for the
MySQL FDW, I get the following errors:

dbname=# CREATE FUNCTION mysql_fdw_handler()
RETURNS fdw_handler
AS '$libdir/mysql_fdw'
LANGUAGE C STRICT;

ERROR:  could not access file "$libdir/mysql_fdw": No such file or directory

dbname=# CREATE EXTENSION mysql_fdw;

ERROR:  could not open extension control file
"/usr/pgsql-9.4/share/extension/mysql_fdw.control": No such file or
directory

What needs to be installed for this to work?

Re: [GENERAL] [ADMIN] $libdir/mysql_fdw

[Ryan King - NOAA Affiliate]

Hi Devrim, I have already installed that:
"Package mysql_fdw_94-2.0.1-1.rhel6.x86_64 already installed and latest
version..."
We're looking into some other options though. Thanks though.

Ryan King
Internet Dissemination Group, Kansas City
Shared Infrastructure Services Branch
National Weather Service
Contractor / Ace Info Solutions, Inc.

On Wed, Oct 21, 2015 at 6:04 AM, Devrim GÜNDÜZ  wrote:

>
> Hi,
>
> On Thu, 2015-08-27 at 12:46 -0500, Ryan King - NOAA Affiliate wrote:
> > ERROR:  could not access file "$libdir/mysql_fdw": No such file or
> > directory
> >
> > dbname=# CREATE EXTENSION mysql_fdw;
> >
> > ERROR:  could not open extension control file
> > "/usr/pgsql-9.4/share/extension/mysql_fdw.control": No such file or
> > directory
> >
> > What needs to be installed for this to work?
>
> Looks like you installed PostgreSQL using the community RPMs -- so make
> sure that you used the RPM of mysql_fdw:
>
> yum install mysql_fdw_94
>
> would do the trick.
>
> Regards,
> --
> Devrim GÜNDÜZ
> Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
> Twitter: @DevrimGunduz , @DevrimGunduzTR
>
>
>