'How to simulate database crash in postgreSQL

I'm new into postgresSQL, in my course in "Advance database" we are given a problem to simulate a crash at given location in postgres, is anyone got an idea how to make a crash? 

BEGIN;
UPDATE actor SET first_name = 'penny' WHERE actor_id = 1;
-- CRASH
COMMIT;

I tested divide by zero but it didn't crash and it only shows an error message! I searched all over the internet but fin nothing about it, anybody knows how to do it?

sqlpostgresql


Solution 1:[1]

You could write a custom function in "C" - that would be able to send the KILL signal.

If this doesn't need to be a frequent occurrence though, you can just call pg_sleep with a suitable delay and then send the signal manually (as root or the postgres user).

Note that this is not exactly what people generally think about when simulating a database crash. What you generally do there is disconnect the power rather than just kill the postgres process itself. That tests the whole path down through the operating system and checks the data really, truly gets persisted to the disk (or not) cleanly. That is a lot more work though, particularly with any sort of interesting (so complex, expensive, slow to restart) disk system.

Solution 2:[2]

A process can commit suicide:

select pg_terminate_backend(pg_backend_pid());

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Solution 3:[3]

To crash Postgres you can overrun memory by setting it higher than is actually available and then running an intensive query. The OS will come in and terminate the postgres service for trying to consume too much memory.

Try this:

SET work_mem to '100GB';
select * 
from pg_class a, pg_class b, pg_class c, pg_class d, pg_class e 
order by random();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Richard Huxton
Solution 2 klin
Solution 3 Bryan Clark

Related Questions