it cannot changed.
Postgres 12 is used.
Andrus.
r when WAL files are copied to other device, copy is perfomed probably in
uncompressed form in windows.
How to set compressed attribute to pg_wal directory in Linux ext4 file system ?
Andrus.
point before it gets restored.
How to decompress it automatically in windows ?
In Linux
restore_command = 'gunzip < "archived_wal/%f" > "%p"'
maybe works.
Will wal_compression=on will produce compressed wal files to additional
compression is not needed?
Andrus.
robably because %f
argument is WAL file name without extension.
How to use compressed WAL files for WAL archieve and restore in windows ?
Andrus.
ain server will also stop with "no space left on device" error.
Or is there some option like to reserve some disk space or limit wal size so
that main server can continue on backup server crash.
Andrus.
ved in backup server and should be used for PITR recovery when
needed.
How to use hot standby server for this ?
Or should separate cluster in 5433 port created and pg_rewind or something
other used for PITR.
Andrus.
.
It this OK ? Or is some better way to implement hot stadby and PITR possibility
in same computer ?
Postgres 12 in Debian is used.
Andrus.
to create standby server which replicates changes using 24 hour delay and data directory backups in
every night.
However this requires separate delayed standby server .
Is there simpler solution, e.q reverse playback of wal segments starting from
current data directory contents.
Andrus.
0+ support for it.
Revoking privileges from view in not required in earlier releases.
Andrus.
ete";
drop role "roletodelete";
But got error
ERROR: role "roletodelete" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public;
How to create script which is guaranteed to delete role ?
This script already contains:
revoke all on schema public,firma1 from "roletodelete" cascade;
Why postgres complains that privileges for schema public depend on this role if
they are revoked ?
How to fix this?
Andrus
ses by database name but those
disappeared in pgAdmin 4.
How to find which users are using database and forcibly disconnect them ?
Andrus.
most of Nodes options in PgAdmin options off but pgadmin still shows
them. It shows also pg_toast schema.
Andrus.
in Debian 10 is used.
Andrus.
ed ilike '%'||'testmiin'||'%' escape '!'
or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
or to_tsvector('english',toode.engnimetus) @@
plainto_tsquery('testmiin')
Andrus.
Hi!
>It is really strange why it is too slow. Can you prepare test case? Looks like
>bug (maybe not Postgres's bug)
Testcase is below.
With jit on it takes 3.3 sec and with jit off 1.5 sec.
Andrus.
create temp table toode ( toode char(20), ribakood c
s variation of this query running with diferent data in different
database in same Debian 10 server. It works fast.
Should I disable jit only for this query or in postgresql.conf permanently?
Andrus.
g Time: 1.729 ms"
"Execution Time: 185.674 ms"
If there are more column expressions, perfomance difference is bigger.
rid table used in column expression contains 1.8 million of rows.
Performance degradation probably occured if upgraded from Postgres 9.1 to
Postgres 12
Since no data is returned query perfomance should be same.
How to fix it ?
Andrus.
Hi!
About your third question, you *never* manually mess with the files in pg_wal.
The server does that.
Is it OK to stop server, delete all files in pg_wal directory and re-start
server ?
Or should default value put back and wait until server frees 1 GB disk space ?
Andrus.
A200A2
000102A200B3
000102A20081 000102A20092 000102A200A3
000102A200B4
000102A20082 000102A20093 000102A200A4
000102A200B5
Andrus.
worker (for the slot monitoring as well as optionally
killing and/or dropping), a simple cron job or even check_postgres.
Where to find some sample how to implement this ?
I read from docs that slot cannot dropped if it is in use.
How to stop replication in this case.
Andrus.
How about implementing"data_reserved_space" setting ?
How to create procedure in server or maybe cron scipt which stops replication
if disk becomes nearly full ?
How to force to stop replication slot in master if it is in use ?
Andrus.
creation (and maybe
stop or disable replication slot).
Postgres 12 in Debian 10 is used.
Andrus.
files by vacuum.
Postgres 12 in Debian is used.
Andrus.
ation.
How you provide sample code how to create view or othe method test so that my
select statement returns data.
Currently select in code
create table test ( test varchar );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space
does not return data.
Andrus.
-writing huge number of sql commands?
Andrus.
ables ?
Is 3 some magical value, how to control it.
Using Postgres 12 in Debian.
Andrus.
at or near "["
LINE 3: .../BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text
How to fix ?
Posted also in
https://stackoverflow.com/questions/60993975/fixing-set-returning-functions-are-not-allowed-in-update
Andrus.
.
Andrus.
.
Andrus.
t support compression or not.
Andrus.
database=mydb SSL enabled
(protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)
Maybe because according to TLSv1.3 standard, compression is no more allowed or
psqlodbc does not pass it to pglib.
How to compress ?
Andrus.
commands are sent and results are retrieved.
Clients have 10-20Mbit download speeds, and 5-20 Mbit upload speeds.
Will data compression increase speed ?
If yes, how to implement this ?
Andrus.
ish_United
States.1252';
also throws error
ERROR: invalid locale name: "English_United States.1252"
So it looks like pg_dump/pg_restore with --create works only from Linux to
Windows and does not work from Windows to Linux.
I expect that it should work from Windows to Linux also.
Andrus.
+Hyper-V+ binary replication allows to connect more
than 20 users and may be best solution.
Both have 64-bit OS. Will binary replication work in this case.
Andrus.
.
pgpool should switch to use it for production work only if main server does
not respond.
I havent found this feature in pgpool documentation (maybe missed).
Andrus.
ited
States.1252';
I verifed that data was restored using pg_restore without manually changing
anything.
Andrus.
two clusters when users continue to enter data, this is not time critical.
Andrus.
console codepages any
way, they operate using only UTF-8 character set since both databases are in
UTF-8
I think console code page warning message is not related to this issue.
Andrus.
bmanually_owner
ENCODING = 'UTF8'
LC_COLLATE = 'Estonian_Estonia.1257'
LC_CTYPE = 'Estonian_Estonia.1257'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
Andrus.
goes down, users can change server address to Windows
computer as temporary workaround and continue working.
Andrus.
--
template0 | postgres | UTF8 | Estonian_Estonia.1257 |
Estonian_Estonia.1257 | =c/postgres +
| | | |
| postgres=CTc/postgres
(1 row)
Andrus.
w template with proper encoding or changing template0
encoding helps?
Andrus.
ation: Yes
Data Execution Prevention Available: Yes
Andrus.
for locale "et_EE.UTF-8":
codeset is "CPUTF-8"
How to fix this ?
Debian and Windows computer have same settings:
Latest Postgres 12 is used
OS and database locales are Estonian
Database encoding is UTF-8
Andrus.
backup from 5432 port directly
to her computer C: drive.
If Postgres 12 32bit pg_dump is not available maybe it is possible to create
stored procedure which invokes pg_dump in server and returns .backup file as
bytea value to client:
select run('pg_dump mydatabase')
Andrus.
--
Adrian Klaver
for data access.
Maybe there is 32 -bit version of pg_dump.exe.
Andrus.
source code.
Andrus.
update on table "rid" violates foreign key constraint
"rid_yhik_fkey"
Andrus.
sted error message details issue long time ago in this pgsql-odbc list
but havent got solution.
I posted it as separate message here and in
https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details
Andrus.
nd latest psqlODBC driver 12.01. are used.
psqlODBC is called from Visual FoxPro
Andrus.
configuration.
Maybe I will change application not to send semicolon before SELECT
statement.
Andrus.
ON andmetp FOR EACH
STATEMENT
EXECUTE PROCEDURE setlastchange()
Andrus.
AS $$
BEGIN
EXECUTE 'DROP TABLE ' || $1;
EXCEPTION WHEN UNDEFINED_TABLE THEN
RETURN;
END;
$$;
Andrus.
looked into odbc description and havent found how error message details
are returned.
Andrus.
d without error.
Andrus.
Hi!
Realized I should have been clearer. By pre-libpq I meant this:
https://odbc.postgresql.org/docs/release.html
psqlODBC 09.05.0100 Release
Changes:
Use libpq for all communication with the server
Previously, libpq was only used for authentication. Using it for all
communication lets
Hi!
Upgrade to a version of Postgres that is not 5 years past EOL?
Not possible at moment because requires lot of testing not to break existing
applications.
Planned in future.
Andrus.
Hi!
Given this is localhost connection, start looking at firewall or AV.
Windows firewall is turned off. It does not affect to local connections in
same computer.
I turned windows antivirus off but problem persists.
Andrus.
OS:
Windows server 2008 R2
Version 6.1 Build 7601
Andrus.
Postgres log:
2020-02-20 15:44:51 EET LOG: server process (PID 3788) was terminated by
exception 0xC005
2020-02-20 15:44:51 EET HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
202
ipt as
text.
Or it retrieves globals from other cluster using dblink and applies changes to
new cluster.
This can be called instead of pq_dumpall and can edited for custom needs.
Editing plpgsql script is easier for postgres users than creating sed script to
delete commands from sql file.
Andrus.
ses appear in file. This looks like
noice.
GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html
does not contain GRANTED BY clause. It looks like pg_dumpall generates
undocumented clause.
Andrus.
-f globals.sql postgres
but this changes user postgres password also.
How to restore roles so that postgres user password is not changed on
restore.
Script runs on Debian 10 with Postgres 12
Server from where it reads users runs on Debian Squeeze with Postgres 9.1
Andrus
all the databases and the
globals.
Source cluster is in old Debian 6 Squeeze running Postgres 9.1
Should I create pipe using pg_dumpall and restore everything from old using
pipe instead of pg_dump/pg_restore ?
Andrus.
bs=4
"database24.backup" &
or some balance between those ?
Is there some postgres or Debian setting which can used during restore time
to speed up restore ?
I use shared_buffers=1GB , other settings from debian installation.
Andrus.
database which does not exist to drop existing database
before restore if it exists ?
Should I invoke
drop database if exists
and
create database
commands before calling pg_restore or can pg_restore do it itself ?
Using Postgres 12 on Debian 10
Andrus.
sorry.
Andrus.
no
dependencies.
Andrus.
server has 11 GB ram . No other applications are running during database
transfer.
shared_buffer=1GB setting is currently used in postgresql.conf
5. Can this plan improved
Andrus.
to restore
whole cluster.
Andrus.
?
Andrus.
old Debian squueze.
Client accesses server from Windows 10 using psqlODBC driver with
sslmode=allow in connection string.
How to verify that connection is encrypted ? Is there some command in client
or server or can some protocol analyzer used if no easier way?
Andrus.
Posted also
')
In 9.1.5 this throws error
ERROR: syntax error at or near "("
LINE 20: lateral xmltable(xmlnamespaces(
^
SQL state: 42601
Character: 582
Andrus.
in both versions?
Is it possible add some CASE WHEN or IF command or any other idea ?
Andrus.
summa
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
Andrus.
CHECK (id > 0)
)
WITH (
OIDS=FALSE
);
How to fix this so that sequences are also restored ?
I tried to restore using both 10.1 and 9.1 pg_restore but autogenerated
primary key columns are still lost.
Posted also in
https://stackoverflow.com/questions/50317935/how-to-ove-database-from-postgres-10-1-to-9-1
Andrus.
101 - 175 of 175 matches
Mail list logo