Related

Product Now Available: Managed PostgreSQL Databases Product
Managed MySQL, Redis, Postgres on DigitalOcean Product
How to connect managed database (postgres) with ssl-mode="varify-full" in django app? Question Question
How can I rename django-project folder to match my local directory tree and keeps things working? Question Question

Question

How to upload postgresql database content to django project on digital ocean

Posted September 24, 2020 228 views
PostgreSQLDjangoDatabases

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?

Add a comment
BiZonNN
By:
BiZonNN
Add a comment

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
marks567217 September 25, 2020
How To Use PostgreSQL with your Django Application on Ubuntu 16.04
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...
Reply Report
Yannek September 25, 2020

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

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.
Reply Report
  • BiZonNN September 25, 2020

    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.

    Reply Report
    • Yannek September 26, 2020

      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.

      Reply Report
      • BiZonNN September 27, 2020

        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

        Reply Report
        • Yannek September 27, 2020 
          @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 ?

          Reply Report
          • BiZonNN September 27, 2020

            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.

            Reply Report
BiZonNN September 29, 2020

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.

Reply Report
  • Yannek September 29, 2020

    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
    Reply Report
    • BiZonNN September 29, 2020

      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.

      Reply Report
      • Yannek September 29, 2020

        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
        Reply Report
BiZonNN September 29, 2020

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

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help