As I was following through the tutorial on this subject https://www.digitalocean.com/community/tutorials/how-to-install-the-django-web-framework-on-ubuntu-18-04
I successfully deployed my django application with postgresql db on digital ocean. However I constantly fail to upload content of the db from my local computer to digital ocean. I always get an empty db after upload. Is there a way to fix it?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
4 answers
by Justin Ellingwood
Django is a flexible framework for quickly creating Python applications. By default, Django applications are configured to store data into a lightweight SQLite database file. While this works well under some loads, a more traditional DBMS can improve performance in...

Hi,

If you need to do initial data upload to your database, consider the method with a dump file. It is quite reliable one. So, make a dump file of a database on your local computer, then send the dump file to your droplet, and restore your database from it on your droplet.

You can obtain detailed info on each step from the following resources:

Creating a database dump file, and restoring a database from a file:
https://www.postgresql.org/docs/13/backup-dump.html

Transferring files using SFTP
https://www.digitalocean.com/community/tutorials/how-to-use-sftp-to-securely-transfer-files-with-a-remote-server

by Justin Ellingwood
SFTP is a secure way to transfer files between local and remote servers. Much more secure than FTP, this protocol uses an SSH tunnel to establish a connection and uses encryption to transfer files in an interactive session.
  • That’s exactly what I tried to do. I have no problem with creating a db dump on my local computer & transferring the dump file to the server. What I need to know is where to restore the file. I can’t find where my database is stored on the server.
    I am exploring the etc directory but still can’t find the database itself.

    • OK, so you have your dump file transferred to your droplet. Now, you need to create your database:

      createdb -U postgres -h localhost -T template0 db_name
      

      Replace db_name with your actual database name.

      Then restore data from the dump file into newly-created database.

      pg_restore -U postgres -h localhost -d db_name dump_file
      

      Replace db_name with your actual database name, and dump_file with your actual dump file. After restoring data, you can list the tables to ensure that everything went fine:

      psql -U postgres -h localhost -W -c "select table_schema, table_name from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog') and table_type = 'BASE TABLE' order by table_schema, table_name;" -d db_name
      

      Let me know how it works.

      • I created the db earlier when I was deploying the project. Anyway, I tried to restore the back_up file in postgres command line. It just switched me to the next line (postgres-#) & at the same time completely screwed my project. Now the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.
        I can’t believe there is now easy way to upload db content to the server

        • @BiZonNN wrote
          Now the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.

          I am guessing now because I do not know what your whole environment is. To me, it looks like your nginx server acts as reverse proxy and got an invalid response from back-end server (embedded Django web server ?). It looks like your Django app serves dynamic content pulling it from psql database.
          So, please, compare the contents of your local database and droplet’s database, and find what differs. To do that you can use SQL queries as well as a tool like pgAdmin.

          How did you create a dump file of your local database ? Can you provide exact command you ran to create it ?

          • It turned out that my problem with ‘502 Bad Gateway nginx/1.14.0 (Ubuntu) had nothing to do with postgres changes.
            Somehow I screwed 'settings.py’ file. Deleted 'django.contrib.auth’ from INSTALLED_APPS.

            As for transferring postgres db contents to DO, I am still working.

            I have created my dbbackup file in pgAdmin4. Moved it to the server. And use console to restore it to the existing db.

            Ubuntu does not let me do it. The problem may be in user permissions. Root user is switched off on my droplet. I am working under another administrative account. I’m gonna try to restore it as a root user.

I am trying to restore the file on DO in DO console using different commands, such as:
psql dbname < dumpfile and others.
It doesn’ work.

  • So, is your droplet’s database still empty ? Can you see any tables in the droplet’s database ? To check it, in the following command replace user postgres and database name db_name with your actual names.

    psql --user postgres --password --host=localhost --db db_name --command '\dt'
    

    What does postgresql’s log say ? List the content of PSQL log directory:

    sudo ls -la /var/log/postgresql/
    

    and replace log_name with the newest listed log file, if it is not empty (size 0), of course :)

    sudo tail -50 /var/log/postgresql/log_name
    
    • My droplet has not been empty since the very beginning. In the process of uploading my django project to DO server I created a postgresql db & successfully copied all my tables from the local computer to this db.
      When I connect to the server db & type \dt, it shows all my tables which makes me think that all them exist.
      The problem is that I can’t copy data which is contained in the tables of the db on my local computer, to the server db.

      • If the structures of your droplet’s database and local database are the same, you can use dump file for the initial data upload.
        It might happen that your created your dump file containing just the database structure (schema), without any data. And every time, when you were trying to restore database from that file on your droplet, you got just the structure. Take a look into your dump file and check if it contains any data, e.g.

        cat dump_file_name | more
        

I opened the las log file & that’s what is says:
“Cannot allocate memory”
“Could not fork autovacuum worker process”
“Worker took too long to start; cancelled
"Autovacuum started without a worker entry”
To me it looks chinese

Submit an Answer