Archive for category PostgreSQL

Thoughts on PostgreSQL JSON Vs MongoDB JSON Performance – Part II

Thoughts on PostgreSQL JSON Vs MongoDB JSON Performance – Part II

Based on my previous post, I have ran the exact same benchmarking tool against MongoDB 3.4 and PostgreSQL 9.6. The reason for this post is to provide an updated results based on the latest version of the databases. However, the results here are NO WHERE near the truth. This test does not show the full potential of MongoDB. The reasons are in the part 1 of the series.

Both PostgreSQL and MongoDB have been minimally tuned with shared_buffer and WT cache size to 50% of the memory. Also filesystem blocksize have been configured to 8K and atime=off.

Sample document in MongoDB

{
{ "_id" : ObjectId("592269630a29bdfd3e2afd35"),
"name" : "AC332359 Phone", 
"brand" : "ACME30387", 
"type" : "phone", 
"price" : 200, 
"warranty_years" : 1, 
"available" : true, 
"description" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin eget elit ut nulla tempor viverra vel eu nulla. Sed luctus porttitor urna, ac dapibus velit fringilla et. Donec iaculis, dolor a vehicula dictum, augue neque suscipit augue, nec mollis massa neque in libero. Donec sed dapibus magna. Pellentesque at condimentum dolor. In nunc nibh, dignissim in risus a, blandit tincidunt velit. Vestibulum rutrum tempus sem eget tempus. Mauris sollicitudin purus auctor dolor vestibulum, vitae pulvinar neque suscipit. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Phasellus lacus turpis, vulputate at adipiscing viverra, ultricies at lectus. Pellentesque ut porta leo, vel eleifend neque. Nunc sagittis metus at ante pellentesque, ut condimentum libero semper. In hac habitasse platea dictumst. In dapibus posuere posuere. Fusce vulputate augue eget tellus molestie, vitae egestas ante malesuada. Phasellus nunc mi, faucibus at elementum pharetra, aliquet a enim. In purus est, vulputate in nibh quis, faucibus dapibus magna. In accumsan libero velit, eu accumsan sem commodo id. In fringilla tempor augue, et feugiat erat convallis et. Sed aliquet eget ipsum eu vestibulum.Curabitur blandit leo nec condimentum semper. Mauris lectus sapien, rutrum a tincidunt id, euismod ac elit. Mauris suscipit et arcu et auctor. Quisque mollis magna vel mi viverra rutrum. Nulla non pretium magna. Cras sed tortor non tellus rutrum gravida eu at odio. Aliquam cursus fermentum erat, nec ullamcorper sem gravida sit amet. Donec viverra, erat vel ornare pulvinar, est ipsum accumsan massa, eu tristique lorem ante nec tortor. Sed suscipit iaculis faucibus. Maecenas a suscipit ligula, vitae faucibus turpis.Cras sed tellus auctor, tempor leo eu, molestie leo. Suspendisse ipsum tellus, egestas et ultricies eu, tempus a arcu. Cras laoreet, est dapibus consequat varius, nisi nisi placerat leo, et dictum ante tortor vitae est. Duis eu urna ac felis ullamcorper rutrum. Quisque iaculis, enim eget sodales vehicula, magna orci dignissim eros, nec volutpat massa urna in elit. In interdum pellentesque risus, feugiat pulvinar odio eleifend sit amet. Quisque congue libero quis dolor faucibus, a mollis nisl tempus."
}

Sample PostgreSQL JSONB Rows. (It is a table with a single JSBON column)

[ RECORD1 ]
data | {"name": "AC321975 Phone", 
"type": "phone", 
"brand": "ACME26236", 
"price": 200, 
"available": true, 
"description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin eget elit ut nulla tempor viverra vel eu nulla. Sed luctus porttitor urna, ac dapibus velit fringilla et. Donec iaculis, dolor a vehicula dictum, augue neque suscipit augue, nec mollis massa neque in libero. Donec sed dapibus magna. Pellentesque at condimentum dolor. In nunc nibh, dignissim in risus a, blandit tincidunt velit. Vestibulum rutrum tempus sem eget tempus. Mauris sollicitudin purus auctor dolor vestibulum, vitae pulvinar neque suscipit. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Phasellus lacus turpis, vulputate at adipiscing viverra, ultricies at lectus. Pellentesque ut porta leo, vel eleifend neque. Nunc sagittis metus at ante pellentesque, ut condimentum libero semper. In hac habitasse platea dictumst. In dapibus posuere posuere. Fusce vulputate augue eget tellus molestie, vitae egestas ante malesuada. Phasellus nunc mi, faucibus at elementum pharetra, aliquet a enim. In purus est, vulputate in nibh quis, faucibus dapibus magna. In accumsan libero velit, eu accumsan sem commodo id. In fringilla tempor augue, et feugiat erat convallis et. Sed aliquet eget ipsum eu vestibulum.Curabitur blandit leo nec condimentum semper. Mauris lectus sapien, rutrum a tincidunt id, euismod ac elit. Mauris suscipit et arcu et auctor. Quisque mollis magna vel mi viverra rutrum. Nulla non pretium magna. Cras sed tortor non tellus rutrum gravida eu at odio. Aliquam cursus fermentum erat, nec ullamcorper sem gravida sit amet. Donec viverra, erat vel ornare pulvinar, est ipsum accumsan massa, eu tristique lorem ante nec tortor. Sed suscipit iaculis faucibus. Maecenas a suscipit ligula, vitae faucibus turpis.Cras sed tellus auctor, tempor leo eu, molestie leo. Suspendisse ipsum tellus, egestas et ultricies eu, tempus a arcu. Cras laoreet, est dapibus consequat varius, nisi nisi placerat leo, et dictum ante tortor vitae est. Duis eu urna ac felis ullamcorper rutrum. Quisque iaculis, enim eget sodales vehicula, magna orci dignissim eros, nec volutpat massa urna in elit. In interdum pellentesque risus, feugiat pulvinar odio eleifend sit amet. Quisque congue libero quis dolor faucibus, a mollis nisl tempus.", 
"warranty_years": 1
}

Raw benchmark results

number of rows 10000000 10 million
PG COPY (ns) 739590735899 12mins 20 secs
PG INSERT (ns) 2770072356801 46mins 10 secs
PG SELECT (ns) 52674489115 52.67secs
PG SIZE (bytes) 14894546944 13.87GB
MONGO IMPORT (ns) 800826044679 13mins 21 secs
MONGO INSERT (ns) 11320674169364 3 hrs 8 mins 41 secs
MONGO SELECT (ns) 596912264977 9 mins 57 secs
MONGO SIZE (bytes) 4295852032 4GB

Summary

The above results have some differences to the EDB’s benchmark results. I have highlighted a few key point.

  • mongoimport have improved tremendously since MongoDB 2.6. The above results are run with a single worker. Now, mongoimport support multiple workers so this result is not longer valid
  • WiredTiger is the default storage engine since MongoDB 3.2 and support compression out of the box. The above results are ran using blockCompressor:snappy

However, the INSERT and SELECT workload for MongoDB is still seriously crap isn’t it? As explained previously, the reason is because the benchmark uses MongoDB shell to insert into the database, which is bounded by the JavaScript engine. Well, JavaScript isn’t exactly a speedy language is it?

At the time of this writing, we are benchmarking PostgreSQL 9.6.5 against MongoDB 3.4.6! We are building our own tool, written in Golang that will be able to squeeze every ounce of performance out of the 2 databases. That ought to be a proper benchmark test!

We will be sharing our results at the Percona Live Conference 2017. If you are going for the conference, please come and say hello :).

Regards,
Wei Shan

Advertisements

Leave a comment

Thoughts on PostgreSQL JSON Vs MongoDB JSON Performance

Thoughts on PostgreSQL JSON Vs MongoDB JSON Performance

Recently, I came across a 3 year old blog post from EnterpriseDB about benchmarking JSON performance on MongoDB Vs PostgreSQL via a random Quora response of someone bashing MongoDB performance. I use both PostgreSQL and MongoDB on a daily basis at my current role. They are both great database and good at what they were built to do. However, the benchmarking results were absolutely unbelievable. I couldn’t believe it and had to read the source code myself.

There were a couple of reasons why the results were highly inaccurate.

INSERTs Test

It uses bash to generate massive INSERT shell scripts and inserts into mongod via Mongo shell. This means that it is not benchmarking PostgreSQL against MongoDB. It is benchmarking PostgreSQL psql against MongoDB Javescript shell (SpiderMoney/v8). That’s not a very fair benchmark is it?

SELECTs Test

Same issue here. It uses Mongo shell to run the SELECT queries. Effectively, the performance will be bounded by the speed of the javascript engine. It was using v8 back in version 2.6. (Link)

Data Loading Test

This is the only fair comparison so far. It compares PostgreSQL’s COPY against MongoDB’s mongoimport. It was indeed true that PosgreSQL was faster than MongoDB in this test.

Summary

I love using both PostgreSQL and MongoDB. However, this benchmarking test fails prove that PostgreSQL was indeed faster than MongoDB in JSON performance. Also, it did not share the configurations of postgresql.conf and mongod.conf. MongoDB doesn’t work well with ext4 filesystem. So if they are using AWS Linux AMI, it’s going to be on ext4 unless they took the effort the mount external EBS volumes.

Since the results are already 3 years old, I plan to benchmark PostgreSQL and MongoDB over the next few weeks using PostgreSQL 9.6 against MongoDB 3.4 with WT storage engine. Also, I don’t work for MongoDB nor EnterpriseDB, this will be a unbiased test 🙂

Regards,
Wei Shan

1 Comment

PostgreSQL 9.6 on RHEL 7.2 -FATAL: semctl(SETVAL) failed: Invalid argument

PostgreSQL 9.6 on RHEL 7.2 -FATAL: semctl(SETVAL) failed: Invalid argument

The error below started occurring once we upgraded to PostgreSQL 9.6 on RHEL 7.2. It was strange because all we did was had a user to login, check the status of pgsql and logout.

 Dec 7 22:50:01 hostname systemd: Created slice user-1004.slice.
 Dec 7 22:50:01 hostname systemd: Starting user-1004.slice.
 Dec 7 22:50:01 hostname systemd: Started Session 2243 of user postgres.
 Dec 7 22:50:01 hostname systemd: Starting Session 2243 of user postgres.
 Dec 7 22:50:01 hostname systemd: Created slice user-0.slice.
 Dec 7 22:50:01 hostname systemd: Starting user-0.slice.
 Dec 7 22:50:01 hostname systemd: Started Session 2244 of user root.
 Dec 7 22:50:01 hostname systemd: Starting Session 2244 of user root.
 Dec 7 22:50:01 hostname postgres[12064]: [432-1] [unknown]: [local]: 58481389.2f20: LOG: connection received: host=[local]
 Dec 7 22:50:01 hostname postgres[12064]: [433-1] postgres: [local]: 58481389.2f20: FATAL: semctl(153976863, 2, SETVAL, 0) failed: Invalid argument
 Dec 7 22:50:01 hostname postgres[21799]: [432-1] : : 58444abd.5527: LOG: server process (PID 12064) exited with exit code 1
 Dec 7 22:50:01 hostname postgres[21799]: [433-1] : : 58444abd.5527: LOG: terminating any other active server processes
 Dec 7 22:50:01 hostname postgres[11514]: [434-1] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: WARNING: terminating connection because of crash of another server process
 Dec 7 22:50:01 hostname postgres[11514]: [434-2] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: DETAIL: The postmasteIP_ADDRESSr has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
 Dec 7 22:50:01 hostname postgres[11514]: [434-3] repmgr: <IP_ADDRESS>(41171): 58480edb.2cfa: HINT: In a moment you should be able to reconnect to the database and repeat your command.
 Dec 7 22:50:01 hostname postgres[12074]: [434-1] [unknown]: ::1(48077): 58481389.2f2a: LOG: connection received: host=::1 port=48077
 Dec 7 22:50:01 hostname postgres[12074]: [435-1] repmgr: ::1(48077): 58481389.2f2a: FATAL: the database system is in recovery mode
 Dec 7 22:50:01 hostname Backup: [INFO]DB check start.
 Dec 7 22:50:01 hostname postgres[21799]: [434-1] : : 58444abd.5527: LOG: all server processes terminated; reinitializing
 Dec 7 22:50:01 hostname postgres[21799]: [435-1] : : 58444abd.5527: LOG: could not remove shared memory segment "/PostgreSQL.1721076088": No such file or directory
 Dec 7 22:50:01 hostname postgres[21799]: [436-1] : : 58444abd.5527: LOG: semctl(152961024, 0, IPC_RMID, ...) failed: Invalid argument
 Dec 7 22:50:01 hostname postgres[21799]: [437-1] : : 58444abd.5527: LOG: semctl(152993793, 0, IPC_RMID, ...) failed: Invalid argument

After researching on the issue, we found this bug :).

Resolution

1) Set RemoveIPC=no in /etc/systemd/logind.conf

2) Reboot the server or restart systemd-logind as follows:
 # systemctl daemon-reload
 # systemctl restart systemd-logind

Reference:

  1. https://bugzilla.redhat.com/show_bug.cgi?id=1287618
  2. https://bugzilla.redhat.com/show_bug.cgi?id=1264533

Note:

  • Please note that I have seen this RHEL 7 issue with Oracle database too. It’s a RHEL 7 bug so it should applies to most database using IPC calls.

Hope this helps.

Regards,
Wei Shan

Leave a comment

Story of recovering a PostgreSQL 9.2 database

Story of recovering a PostgreSQL 9.2 database

Background Story

It was a 2 node PostgreSQL 9.2 streaming replication setup. The master node crashed without possibility of recovery. Thus, there was a need to restore the standby database to resume the production services. The standby database also reboot when master node crashed. The standby node was performing database backup via pg_start_backup when it rebooted.

I will simulate the recovery below.


postgresql.conf

wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.2/data/pg_archive/%f'

recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=10.1.0.3 port=5432 user=postgres application_name=testdb keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
restore_command = 'cp /var/lib/pgsql/9.2/data/pg_archive/%f %p'
recovery_target_timeline = 'latest'

Start PostgreSQL backup on the database

postgres=# select pg_start_backup('test1');
 pg_start_backup
-----------------
 0/40000B8
(1 row)

Abort PostgreSQL database and start it again. (Notice we are not able to login the psql)

-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -m immediate stop
waiting for server to shut down... done
server stopped
-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start
server starting
-bash-4.1$ psql
psql: FATAL: the database system is starting up

Notice that the startup process is waiting for a WAL file.

-bash-4.1$ ps -aef | grep postgres
root 2405 2283 0 18:19 pts/1 00:00:00 su - postgres
postgres 2407 2405 0 18:19 pts/1 00:00:00 -bash
postgres 2921 1 0 19:21 pts/1 00:00:00 /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
postgres 2923 2921 0 19:21 ? 00:00:00 postgres: logger process
postgres 2924 2921 0 19:21 ? 00:00:00 postgres: startup process waiting for 000000010000000000000004
postgres 2925 2921 0 19:21 ? 00:00:00 postgres: checkpointer process
postgres 2926 2921 0 19:21 ? 00:00:00 postgres: writer process

Error logs in /var/log/messages

FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: could not connect to the primary server: could not connect to server: Connection timed out
 Is the server running on host "10.1.0.3" and accepting
 TCP/IP connections on port 5432?

cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/000000010000000000000004': No such file or directory
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/000000010000000000000004': No such file or directory
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
LOG: received smart shutdown request
FATAL: terminating walreceiver process due to administrator command
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down in recovery at 2016-08-03 19:17:49 SGT
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
LOG: entering standby mode
LOG: restored log file "000000010000000000000004" from archive
LOG: redo starts at 0/4000020
LOG: record with zero length at 0/40000B8
LOG: record with zero length at 0/40000B8
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
FATAL: the database system is starting up

The WAL file that it was waiting for existed in pg_xlog but not in pg_archive.

-bash-4.1$ ls /var/lib/pgsql/9.2/data/pg_archive
000000010000000000000002 000000010000000000000003

-bash-4.1$ ls /var/lib/pgsql/9.2/data/pg_xlog
000000010000000000000002 000000010000000000000003 000000010000000000000004

Solution: Remove recovery.conf and restart PostgreSQL. Noticed the we didn’t have to use pg_stop_backup to remove the backup label.

-bash-4.1$ mv recovery.conf recovery.done
-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start
server starting
-bash-4.1$ psql
psql (9.2.17)
Type "help" for help.

postgres=# select pg_stop_backup();
ERROR: a backup is not in progress

Verification (Formatted for readability)

-bash-4.1$ /usr/pgsql-9.2/bin/pg_controldata
Database cluster state: in production
pg_control last modified: Wed 03 Aug 2016 07:22:27 PM SGT
Latest checkpoint location: 0/40000B8
Prior checkpoint location: 0/4000058
Latest checkpoint's REDO location: 0/40000B8
Minimum recovery ending location: 0/0
Backup start location: 0/4000020
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: hot_standby

Summary

There is a few learning points from the story.

  • When a database crashed while it is running pg_start_backup() mode, after it reboots, it is not required to run pg_stop_backup().
  • The reason why it was waiting for the WAL file that was in pg_xlog is due to recovery.conf. The restore command in recovery.conf was pointing to pg_archive to restore the WAL file.
  • It was a crash recovery, we didn’t have to use recovery.conf to restore the database. recovery.conf is only used when we are restoring the database from backup

Regards,
Wei Shan

Leave a comment

How to generate authentication file for PgBouncer

How to generate authentication file for PgBouncer

PgBouncer is a lightweight connection pooler that prevents the MAX_CONNECTION reached error in PostgreSQL. Basically, it does a very good job for what it says. It maintains a pool of connections for your applications. This helps in reducing the overhead of setting up and tearing down connections for every client incoming request. [link]

By default, it uses a plain text password file for authenticating users to the PostgreSQL database. This will obviously raise a red flag during security audit! Let’s see what we can do to prevent that red flag 🙂

First, we have to install pg_bouncer and psycopg2.

yum install pgbouncer.x86_64 python-psycopg2.x86_64

Now that we got PgBouncer installed. We have to do configuration.

# cd /etc
# chown -R postgres. pgbouncer/
# su - postgres
$ cd /etc/pgbouncer/
$ ls
mkauth.py mkauth.pyc mkauth.pyo pgbouncer.ini

Now, let’s generate that password file 🙂

$ ./mkauth.py "/etc/pgbouncer/password.txt" "dbname='postgres' user='postgres' host='localhost'"
$ ls
mkauth.py mkauth.pyc mkauth.pyo password.txt pgbouncer.ini

Let’s see if we can view the password!

$ cat password.txt
"postgres" "" ""
"repmgr" "md58ea99ab1ec3bd8d8a6162df6c8e1ddcd" ""
"testuser" "md5d70a0452418aeb8fb4030eae69ca2856" ""

It uses the pg_shadow table and generate the password file. Obviously, it is only salted and double hashed with MD5. It is not as secure as SHA-256, but still a lot more secure that reading the password in plain text isn’t it?

Regards,
Wei Shan

Leave a comment

WARNING – Granting of psql/sqlplus executables to sudoers in Linux/Unix

WARNING – Granting of psql/sqlplus executables to sudoers in Linux/Unix

I discovered this behavior accidentally during my project work recently. I needed psql access to a RHEL 6 system. However, the company policy enforces a minimum access policy (Well done!). Thus, my account was given a sudoer permission to only use psql executable. I needed to perform DBA tasks on this system.

Let me show you the issue.

[weishan]$ sudo su -
[sudo] password for weishan:
ユーザー user は'/bin/su -' を root として  上で実行することは許可されていません。すみません。
(This means user not allowed to execute su -)

[weishan]$ tail -n 10 /var/log/messages
tail: `messages' を 読み込み用でオープンできません: 許可がありません
(This means access denied)

[weishan]$ sudo /home/postgres/pgsql/bin/psql -h localhost -p 5432 -U postgres postgres
[sudo] password for weishan:
psql (9.2.4)
Type "help" for help.

Looking at the output above, the sudoer was configured correctly! Now, let’s see..

psql> \!
[root@gsdb03s log]# tail -n 5 /var/log/messages
May 10 15:34:47  snmpd[22564]: Connection from UDP: [ip]:63484->[ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]
May 10 15:34:47 snmpd[22564]: Connection from UDP: [ip]:63484-> [ip]

From the above, we can show that it is technically possible for unintended DBAs to get root access even if you did not allow them access explicitly. Thus, we should be mindful while granting psql or sqlplus executable.

I did not test this on sqlplus, but the behaviour should be the same since both psql and sqlplus allow the user to “temporarily” exit the console to OS level access.

Moral of the story:

  1. Do not configure sudo for user to access psql/sqlplus to login to postgres as root user directly.
  2. Configure sudo to only allow weishan user to “sudo su -” to postgres user.
  3. Configure logging for sudo access

Regards,
Wei Shan

 

 

Leave a comment

Playing Around with PostgreSQL date/time Datatype

Playing Around with PostgreSQL date/time Datatype

To check current timestamp. (Notice it’s GMT+8 in the results)

postgres=# select now();
 now
-------------------------------
 2016-02-29 14:19:36.046499+08
(1 row)

To check current timestamp 1 hour ago.

postgres=# select (now() - interval '1 hour');
 ?column?
-------------------------------
 2016-02-29 13:20:47.030513+08
(1 row)

To check current timestamp 1 day ago.

postgres=# select (now() - interval '1 day');
 ?column?
-------------------------------
 2016-02-28 14:20:53.820484+08

To check current timestamp in epoch time. (Unix timestamp in seconds)

postgres=# select extract(epoch from NOW());
 date_part
------------------
 1456726955.54147
(1 row)

To check current timestamp in epoch time. (Unix timestamp in milliseconds)

postgres=# select extract(epoch from NOW())*1000;
 ?column?
-----------------
 1456727010006.5
(1 row)

Bonus! By default, the output is integer. You need to cast it to float to get results with less than 0.

postgres=# select 1/2;
 ?column?
----------
 0
(1 row)
postgres=# select 1/2::float;
 ?column?
----------
 0.5
(1 row)

I had quite some “fun” playing with all these date/time datatype recently.

Regards,
Wei Shan

 

Leave a comment

PostgreSQL 9.x – pg_basebackup: could not get transaction log end position from server: FATAL: archive member “core.56229” too large for tar format

PostgreSQL 9.x – pg_basebackup: could not get transaction log end position from server: FATAL:  archive member “core.56229” too large for tar format

While I was trying to re-deploy a PostgreSQL streaming replication slave from the master node, I hit the following error:

[root@node2~]# su - postgres
-bash-4.1$ /usr/pgsql-9.2/bin/pg_basebackup -D /data/var/lib/pgsql/9.2/data -h node1 --xlog -P -v
transaction log start point: 70/CD000020
166537602/235436024 kB (100%), 2/2 tablespaces
pg_basebackup: could not get transaction log end position from server: FATAL: archive member "core.56229" too large for tar format

From node1, I found the following files in the $PGDATA:

4.2G ./core.53808
4.2G ./core.55595
4.2G ./core.55698
4.2G ./core.56229

The above was the core dump from previous PostgreSQL DB crashes. By default, PostgreSQL does not create files larger than 1G, so I figured this is not the datafiles. I asked Uncle Google and found out that it’s the core dump by PostgreSQL DB crashes.

Once I removed the files from the $PGDATA directory. The pg_basebackup command executes successfully. The interesting thing that I learnt is, pg_basebackup uses tar in the backend to compress and send the files over to the slave database.

Regards,
Wei Shan

Leave a comment