I had trouble interrupting a long-running command in psql and eventually crashed the database with a kill -9 (yes, not a good thing to do!). Help! Is there some way to have the system come up without trying to restore the state that it was in when I crashed the system? The tables that were being formed are not of any use to me. When I run an strace on the pid for the bin/postgres command, I get stuff such as the following over and over again, and I have no idea how to use the information: select(7, [5 6], NULL, NULL, {tv_sec=42, tv_usec=457883}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 21793 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=21793, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 21793 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22420 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 10 read(10, "6510\n/zfs/postgres\n1614281187\n54"..., 8191) = 94 close(10) = 0 getpid() = 6510 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=22420, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 22420 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22518 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=22518, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 22518 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22661 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 10 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=21793, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 21793 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22420 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 10 read(10, "6510\n/zfs/postgres\n1614281187\n54"..., 8191) = 94 close(10) = 0 getpid() = 6510 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=22420, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 22420 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22518 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=22518, si_uid=111, si_status=0, si_utime=0, si_stime=0} --- wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 22518 wait4(-1, 0x7ffc0613ca34, WNOHANG, NULL) = 0 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 select(7, [5 6], NULL, NULL, {tv_sec=60, tv_usec=0}) = ? ERESTARTNOHAND (To be restarted if no handler) --- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=6515, si_uid=111} --- getpid() = 6510 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f7dbe76eb10) = 22661 rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call) rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0 openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 10 read(10, "6510\n/zfs/postgres\n1614281187\n54"..., 8191) = 94 close(10) = 0 getpid() = 6510 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 |
On Fri, Feb 26, 2021 at 9:15 AM Celia McInnis <[hidden email]> wrote:
What you are trying to achieve sounds like restoring a backup, do you have any available? Regards, Juan José Santamaría Flecha |
Alas I didn't bother making a backup since It is a research database. Still it is large enough that I'd rather not do a dump/restore now of the tables that I'd want to dump/restore. Is there some other neat alternative that I can do to just terminate postgresql's attempt to clean up after I messed it up? BTW I don't know the name of the table that it was trying to create since my stored procedure had generated a name starting with "tmp" and having a time stamp and other info after that. Originally I was using a temporary table, but had made it permanent to help debug... On Fri, Feb 26, 2021 at 7:46 AM Juan José Santamaría Flecha <[hidden email]> wrote:
|
Free forum by Nabble | Edit this page |