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.
?
Andrus.
summa
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
Andrus.
in both versions?
Is it possible add some CASE WHEN or IF command or any other idea ?
Andrus.
')
In 9.1.5 this throws error
ERROR: syntax error at or near "("
LINE 20: lateral xmltable(xmlnamespaces(
^
SQL state: 42601
Character: 582
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
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.
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.
to restore
whole cluster.
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.
sorry.
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.
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.
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.
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
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.
AS $$
BEGIN
EXECUTE 'DROP TABLE ' || $1;
EXCEPTION WHEN UNDEFINED_TABLE THEN
RETURN;
END;
$$;
Andrus.
ON andmetp FOR EACH
STATEMENT
EXECUTE PROCEDURE setlastchange()
Andrus.
looked into odbc description and havent found how error message details
are returned.
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
d without error.
Andrus.
configuration.
Maybe I will change application not to send semicolon before SELECT
statement.
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.
update on table "rid" violates foreign key constraint
"rid_yhik_fkey"
Andrus.
source code.
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
in Debian 10 is used.
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.
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.
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.
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
A200A2
000102A200B3
000102A20081 000102A20092 000102A200A3
000102A200B4
000102A20082 000102A20093 000102A200A4
000102A200B5
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.
0+ support for it.
Revoking privileges from view in not required in earlier releases.
Andrus.
most of Nodes options in PgAdmin options off but pgadmin still shows
them. It shows also pg_toast schema.
Andrus.
ed ilike '%'||'testmiin'||'%' escape '!'
or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
or to_tsvector('english',toode.engnimetus) @@
plainto_tsquery('testmiin')
Andrus.
-writing huge number of sql commands?
Andrus.
ables ?
Is 3 some magical value, how to control it.
Using Postgres 12 in Debian.
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.
files by vacuum.
Postgres 12 in Debian is used.
Andrus.
creation (and maybe
stop or disable replication slot).
Postgres 12 in Debian 10 is used.
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.
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.
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.
for data access.
Maybe there is 32 -bit version of pg_dump.exe.
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.
ation: Yes
Data Execution Prevention Available: Yes
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
w template with proper encoding or changing template0
encoding helps?
Andrus.
--
template0 | postgres | UTF8 | Estonian_Estonia.1257 |
Estonian_Estonia.1257 | =c/postgres +
| | | |
| postgres=CTc/postgres
(1 row)
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.
t support compression or not.
Andrus.
.
Andrus.
bmanually_owner
ENCODING = 'UTF8'
LC_COLLATE = 'Estonian_Estonia.1257'
LC_CTYPE = 'Estonian_Estonia.1257'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
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.
.
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.
+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.
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.
goes down, users can change server address to Windows
computer as temporary workaround and continue working.
Andrus.
.
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.
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.
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.
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.
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.
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.
may occur ?
Will pg_dump/pg_restore in Windows server fix all issues.
Andrus.
T)
TABLESPACE pg_default;
Maybe it is possible to force postgres in windows to use the same locale as in
Linux. Locales are actually the same.
Andrus.
Hi!
Are you referring to two different instances of Postgres on Windows?
No.
Main server is in Linux and backup server is in windows.
Andrus.
and
pg_basebackup .
Can this backup used for PITR in Linux ?
Andrus.
also, e-q forcing same local in postgres.conf
Andrus.
ate: XX002
Andrus.
it cannot changed.
Postgres 12 is used.
Andrus.
.2ndquadrant.com/en/blog/icu-support-postgresql-10/
Andrus.
that when new database is added or new tables are added to
database they will start replicate automatically ?
Will it require more powerful backup server to replay main server sql stream
from different databases.
Andrus.
to
specify recovery target time
for applying sql statements to base backup.
Is PITR recovery supported only using binary WAL files ?
Other limits can probably be solved.
Andrus.
wal_keep_segments= 360 # was 180
Will this allow replication to start after pg_basebackup ?
According to doc min_wal_size and wal_keep_segments both keep the minimum
number of wal segments for replication.
Why those parameters are duplicated?
Andrus.
tting
wal_keep_segments to a value large enough to ensure that WAL segments
are not recycled too early, or by configuring a replication slot for the
standby.
Will wal_keep_segments keep segments also if named replication slot is lot
used ?
Andrus.
--recursive --verbose 0700 /var/lib/postgresql/12/main
chown -Rv postgres:postgres /var/lib/postgresql/12/main
/etc/init.d/postgresql start
In other words why do a pg_basebackup if you have a standby receiving WALs?
I dont receive WALs.
Andrus.
/12/main
chmod --recursive --verbose 0700 /var/lib/postgresql/12/main
chown -Rv postgres:postgres /var/lib/postgresql/12/main
/etc/init.d/postgresql start
Andrus.
that cluster is tranferred over internet faster? Maybe it can transferred in compressed form over
internet.
Andrus.
"root".
I tried
sudo --user=postgres pg_basebackup
but got error
could not change directory to "/root": Permission denied
Andrus.
ktop alter baas type char(8) collate ucs_basic
fixes the issue.
Is this fix reasonable ?
What other issues may occur ?
Can base backup created in windows using pg_basecakup used in Linux without
such fix?
Andrus.
starting from Postgres 9.0
Posted also in
https://stackoverflow.com/questions/63032072/how-to-create-function-returning-value-up-to-first-non-digit-decimal-charcater#63032126
Andrus.
-2020-06-19_00.log
How get yesterday log file from remote client application using postgresql
query ?
Using Postgres 12 in Debian.
Andrus.
relation_size(c.oid) desc
Andrus.
only 2 (sum(shop.quantity) for shoes).
Most sold shoe in warehouse is SHOE2
So SHOE2 should be moved to shop from warehouse.
How to find products which should moved ?
Can some SELECT with window function used for this ?
PostgreSQL 9.3.5 is used.
Andrus.
Posted also in
https://stackoverflow.c
this different from source position.
How to fix this so that 3 last tokens are returned in message like
Syntax error at or near ' i > )'
Andrus.
ot;testuser";
revoke all on kasutaja,kaspriv,logifail from "testuser" cascade;
grant select on kaspriv,kasutaja to "testuser";
grant update (eesnimi,
nimi,email,amet,islocked,telefon,language,vabakuup,kasilfirma) on
kasutaja to "testuser";
grant insert on logifail to "testuser";
Using
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Andrus.
"pg_default_acl_role_nsp_obj_index"DETAIL: Key (defaclrole,
defaclnamespace, defaclobjtype)=(30152, 186783649, r) already exists.
Should duplicate schema names accepted or should their usage throw
better error messages.
Andrus.
owner unchanged ?
Andrus.
w to find and delete all large objects in database ? Maybe it is
created accidently .
Using
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Andrus.
1 - 100 of 175 matches
Mail list logo
