Problem Restoring PostgreSQL Database in Docker Container with Symfony

Question

I'm developing a Symfony application to backup and restore databases. My application works fine on the Symfony server, but I'm facing issues when trying to use it with my dockerized setup.

Context:

I have two PostgreSQL databases: the main one named "potter" and a backup one named "backup." I can successfully create a dump of these databases and store the SQL file in my Apache-PHP container at /var/www/var/dump/.

When I try to restore the main database to the backup, it works perfectly only when I execute the following command in my project terminal:

docker exec -it safebase-backup-1 psql -U user -d backup -f /var/www/var/dump/potter_dump_02-10-2024_14-05-08.sql

However, when I run the same command in another context (e.g., from my application), I get the following error:

The command "'docker' 'exec' '-i' 'safebase-backup-1' 'psql' '-U' 'user' '-d' 'backup' '-f' '/var/www/var/dump/potter_dump_02-10-2024_14-05-08.sql'" failed. Working directory: /var/www/public Error: proc_open(): posix_spawn() failed: No such file or directory.

Questions:

Why does the command work in the terminal but fail when executed from my application?

 docker exec -it safebase-backup-1 psql -U user -d backup -f /var/www/var/dump/potter_dump_02-10-2024_14-05-08.sql

How can I resolve the "proc_open(): posix_spawn() failed" error?

Any help or suggestions would be greatly appreciated!

Thank you!

My code:

RestoreService:

<?php
namespace App\Service;

use Symfony\Component\Process\Exception\ProcessFailedException;
use Symfony\Component\Process\Process;

class RestoreService
{
    public function restoreDatabase(string $fileName, string $databaseName): bool
    {
        $targetContainer = $this->getTargetContainer($databaseName);

        $restoreCommand = [
            'docker', 'exec', '-i', $targetContainer, 'psql', '-U', 'user', '-d', $databaseName, '-f', "/var/www/var/dump/$fileName"
        ];
        $this->executeProcess($restoreCommand);

        return true;
    }

    private function getTargetContainer(string $databaseName): string
    {
        switch ($databaseName) {
            case 'potter':
                return "safebase-database-1";
            case 'backup':
                return "safebase-backup-1";
            default:
                throw new \InvalidArgumentException("Database unknown : $databaseName");
        }
    }


    private function executeProcess(array $command): void
    {
        $process = new Process($command);
        $process->run();

        if (!$process->isSuccessful()) {
            throw new ProcessFailedException($process);
        }
    }
}

My controller :

 #[Route('/backlog/restore/{id}', name: 'app_backup_restore')]
    public function restoreForm(BackupLog $backupLog, ManagerRegistry $doctrine, Request $request): Response
    {
        $filePath = $backupLog->getFilePath();

        if (!file_exists($filePath)) {
            $this->addFlash('error', 'Le fichier de sauvegarde n\'existe pas.');
            return $this->redirectToRoute('app_backups');
        }

        $databases = ['potter', 'backup'];
        $form = $this->createForm(RestoreDatabaseType::class, null, ['databases' => $databases]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $data = $form->getData();
            $databaseName = $data['database'];

            try {
                // Passer uniquement le nom du fichier à la méthode de restauration
                $this->restoreService->restoreDatabase(basename($filePath), $databaseName);
                $this->addFlash('success', 'La base de données a été restaurée avec succès.');
            } catch (\Exception $e) {
                $this->addFlash('error', 'Erreur lors de la restauration : ' . $e->getMessage());
            }

            return $this->redirectToRoute('app_backups');
        }

        return $this->render('backlog/restore.html.twig', [
            'form' => $form->createView(),
            'backupLog' => $backupLog,
        ]);
    }

David Maze · Accepted Answer · 2024-10-02 14:06:56Z

Don't use docker exec for routine database operations. It's unnecessary and gives the caller unrestricted root-level access over the entire system.

If you're just trying to run a psql command to restore the data, then I'd remove the subprocess call entirely. The dump file contains a sequence of SQL commands that reconstruct the database, you can can execute these the same way you'd run any other SQL command. Create a normal database connection using your application framework, read the contents of the dump file, and execute them via the database connection.

If you really need to use psql, then I'd run it directly in the container, not via docker exec. You'd need to make sure it's installed in your Dockerfile

RUN apt-get update \
 && DEBIAN_FRONTEND=noninteractive apt-get install -y postgresql-client

and then when you go to invoke it, pass the database container name via the psql -h (as a host name), without docker exec.

$restoreCommand = [
  'psql', '-h', $targetContainer, '-U', 'user', '-d', $databaseName, '-f', "/var/www/var/dump/$fileName"
];

In both cases note that the dump file needs to be in the application container and not the database container; the docker exec path would require it to be in the database container and not the application container.

Also in both cases, the application and the database need to be on the same Docker network. If you're launching both of these in the same Compose file this happens automatically (I tend to recommend deleting all manual networks: setup); if you're using manual docker run commands you need to make sure you've docker network created a network are are docker run --net both containers on the same network.

As I mentioned in the first paragraph, if you can run any docker command at all, then you can all but trivially root the entire host system. Containers do not normally have the Docker socket available to them to avoid this possibility. Container images also don't normally have any more software or tools than they need to run their specific application.

Your posix_spawn() failed: No such file or directory error basically translates to "the container doesn't have a docker binary". I'd try hard to avoid using this at all. Either of the paths described above, using a direct database connection or a local copy of psql, will work in any container or non-container environment. If you've taken a hard dependency on a docker CLI tool then it won't work in non-container environments, for unprivileged users, or on non-Docker container environments like Kubernetes.

Woow ! Thank you very much! Your explanations are very clear and it works! I spent 3 days trying to understand by asking AIs who only said nonsense. What a pleasure to read a human with a great dev experience that allowed me not to waste another three days and to understand! Thank you very much again for the time you used for me to answer me.

Collectives™ on Stack Overflow

Problem Restoring PostgreSQL Database in Docker Container with Symfony

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related